Calculate and Plot Percent Mix

[mur2za]

Hey guys, I am trying to create a line and column graph that plots sales by product (column) and the percentage of total sales of each product (line). The first part is obviously simple - I can't figure out the DAX to crate a column that generates the percent of each product's sales against the total sales...

Sample Data:

Product A - Category Bottles - Sales 10

Product B - Categroy Bottles - Sales 20

Product C - Category Bottles - Sales 30

Product D - Category Glasses - Sales 20

Product E - Categroy Decanters - Sales 30

Total Sales - 110

I only care about Bottles, so my report is filtered down to just Bottles via slicer. Plotting the column chart is easy - Products A, B, C along the X axis and sales along the Y axis. But how do I remove the filter and calculate the % Mix = [Product Sales]/[Total Sales] to plot the line graph?

I tried % Mix = SUM(Sales[Product Sales])/CALCULATE(SUM(Sales[Product Sales]), ALL(Sales[Category])), but results were way off.

Any ideas??

Much appreciated!

mur2za.

[Baskar]

Hi Mur2za,

Cool buddy we can do.

1. Drag the sales in column from here u will get the column , i think no issue for you on this, okay go

2. Again Drag the same sales in line ,

   a. from the sales in line chart click the "Down Arrow" then u can see the option like "Quick Calc" click that one.

This is the new feature added in power BI latest edition. It will help to solve your prob.

From there choose "Percent of grand total" from "Show value as" box

Hope it will help u, if not let me know

[mur2za]

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?

[Sean]

@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!

[mur2za]

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?

[Sean · Accepted solution]

@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.

[mur2za]

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.