Forum Discussion

mur2za's avatar
mur2za
Frequent Visitor
10 years ago
Solved

Calculate and Plot Percent Mix

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.

 

Need Help
  • Sean's avatar
    Sean
    10 years ago

    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.

     

     

6 Replies

  • Baskar's avatar
    Baskar
    Resident Rockstar
    10 years ago

    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's avatar
      mur2za
      Frequent Visitor
      10 years ago

      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's avatar
        Sean
        Community Champion
        10 years ago

        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!