1 of 1 people found this helpful
Refer the below snapshot. If this is it. I have attached the updated your Dummy_Data and sample workbook.
After Re-design the data, you just need to pivot the data and rename as required.
I hope this could help you.
See my solution attached
First I pulled in your excel sheet and pivoted the data around the product ID
Then I created a field zn(revenue) to zero out the nulls
the dummy data did not have DATE so I just removed that from the fixed calculation (you will put it back in) - I called it Initial Fixed Calcualtion
The key here is to take the calculation to the filter shelf and select All Values and then add it to Context (right click the field and select Add to Context
It sets the calculation prior to filtering
so your data unfiltered would look like this
And filtered like this
The fixed totals remain the same as does the market share against all competitors - which I believe was the goal
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
market share file.twbx 77.0 KB
Thank you for the answer. Your solution is perfect given what I told you about the problem, but, fault of my own, I don't think I've explained it clearly enough.
I am sourcing the data from an Amazon Redshift database via a Custom SQL. The data therefore comes in the form as attached in the 2nd tab of the excel worksheet below. As it is not static data and connecting live to the source, I cannot modify it in excel to make it suitable for a pivot. The only options I can think of are either to create the pivot in SQL (which I do not know how to do/is possible) or find another solution on tableau.
* The data column in the sample I gave you is just for one day. The SQL query is sourcing for the last 12 months.
Thanks again for all your help,
Dummy_Data2.xlsx 17.3 KB