I guess you visited this post.
the difference is you have dimension of Country.
Without this dimension, I think the behavior is close to your expectation.
To have each country's value, you need to prepare one more field to link as country.
This brings another complication. and this is not a good way to use Tableau first of all.
Yes I did saw the post How to add all sales to get total sales in table (Profit and Loss statement)
And that's where I try to imitate, even going through the length in having 3 different sets of data into 1.
However, like you said, when I introduced the 'Country' dimension, it didn't work to how I'd like it to be.
You mentioned that in order for me to have each country value, I'd need to prepare one more field to link as country. But since my 'TableauRelationship' purpose is really just for the 'categories' which acts as Headers, how should I link it to the country then?
Wow...so for each 'country' that I have, I need to create the 'header' for it.
But why is that? What is the logic behind Tableau that's different to PowerPivot?
Still, your solution does solves my question, although it isn't what I had hoped for.
Thus will accept it to be the 'correct answer'
I don't know power pivot very well. But one thing, excel and Tableau is way different application and Tableau wants Flat data.
Don't try to replicate what you did with excel, but try to understand the feature.
In that sense, I try to explain the reason you need to add each country.
In the matrix you are trying to show, each "Cell" need to have "place holder" which Tableau put the calculation result onto it.
Without "country" as dimension, Tableau does not have place to put the numbers in.
The beset way to have data though is that
- only have data on smallest granularity level and total matches to G.Total. (No duplicated data)
- Negative needs to be there as negative.
- Create dimension group to show sub total.