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

     

     

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!

      • mur2za's avatar
        mur2za
        Frequent Visitor
        10 years ago

        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?