Forum Discussion
Calculate and Plot Percent Mix
Thank you Baskar, but that does not work in my case. My dataset is far bigger than the sample I provided, and as such, my report is limited to just one Category (Bottles, in my example). With the "Quick Calc" option, it onoly calculates % against the total of the Bottles category, not against the Total Sales of the company...
What I need is a way to have my report filtered to just Bottles, but still calculate the % mix of products A, B and C against the total company sales. Make sense?
mur2za Create these 4 Measures...
You can obviously write 1 Measure to do all this BUT as Rob Collie says
Never write the same formula twice! - so breaking it down now will help you later on.
Total Sales = SUM(Sales[Product Sales]) Bottle Sales = CALCULATE([Total Sales], FILTER(Sales, Sales[Category]="Bottles")) Overall Sales = CALCULATE([Total Sales], ALL(Sales)) % Measure = DIVIDE([Bottle Sales], [Overall Sales], 0)
Then place Bottle Sales Measure in the Columns Values and % Measure in the Line Values
(and this way you don't need a slicer)
Hope this helps!
Thanks Sean. I already have several other charts on my report, so it is already sliced to Category.
Actually, I am managing the report for the entire org, and different teams own different Product Categories, so I have an ALL tab for the entire org sales, and then multiple tabs each sliced to a specific Product Category. So, doing away with the slicer is not an option...
The % Mix was a new request that came in after the report was already built and in use. So, now trying to add % Mix to my "Sales by Product" chart. And hence the difficulty of calculating % Mix against Total Sales.
In your solution, how is Overall Sales different from Total Sales? I am wondering why my ALL function didn't work.. I have ALL(Sales[Category]) - should I not have the column specified in the ALL function?
mur2za Yes to get the overall total at ALL levels - don't specify a column!
Look at the picture and you'll see what I mean.
Your function did exactly what you asked it to do => gave you the overall total but only at the Catgeory Level.
Yup, that worked! I removed the "Category" column from my ALL function and it gave me the correct results!
Thanks for your help Sean!
Mur2za.