Forum Discussion

ghetus's avatar
ghetus
Frequent Visitor
9 years ago
Solved

Percent of group total

Hello,   I want create report in which will be sum of sales and percent from sum on group. I know in Power BI exists Quick measures, but they calculate percent from grand total, i need calculate pe...
Need Help
  • tringuyenminh92's avatar
    tringuyenminh92
    9 years ago

    Hi ghetus,

     

    This is first time I'm facing with % total of multiple levels, so I separate it into 3 measures and use a trick with If condition to showing 3 measures for 3 levels.

     

    • Create % for the smallest level (items)

     

    % level 3 = DIVIDE(sum('Sales Data'[Sales]),CALCULATE(sum('Sales Data'[Sales]),ALLSELECTED('Sales Data'[Item])  ) )
    • The Second level

     

    % level 2 = DIVIDE(sum('Sales Data'[Sales]),CALCULATE(sum('Sales Data'[Sales]),ALLSELECTED('Sales Data'[Category])  ) )
    • And the first level (markets)
    % level 1 = DIVIDE(sum('Sales Data'[Sales]),CALCULATE(sum('Sales Data'[Sales]),ALLSELECTED('Sales Data'[Market])  ) )

     

    As my observation, when showng 1 level in matrix, the parent will be 100% or 1, so I will use IF condition to replace this value to its percentage in the previous level

    • Create measure for Level 2:

     

    Level 2 = if([% level 3]=1 , [% level 2],[% level 3])

     

    • Finally, create measure for level 1 with name % group:
    % group = if([Level 2]= 1,[% level 1] ,[Level 2])

     

     

    For more details, please kindly check my sample file and data for your case.

    It's late in Vietnam so i'm going to bed now, hope you take a look with some testing to ensure this is correct for all cases.

     

    If this works for you please accept it as solution and also like to give KUDOS.

    Best regards
    Tri Nguyen

tringuyenminh92's avatar
tringuyenminh92
Icon for Memorable Member rankMemorable Member
9 years ago

Hi ghetus,

 

This is first time I'm facing with % total of multiple levels, so I separate it into 3 measures and use a trick with If condition to showing 3 measures for 3 levels.

 

  • Create % for the smallest level (items)

 

% level 3 = DIVIDE(sum('Sales Data'[Sales]),CALCULATE(sum('Sales Data'[Sales]),ALLSELECTED('Sales Data'[Item])  ) )
  • The Second level

 

% level 2 = DIVIDE(sum('Sales Data'[Sales]),CALCULATE(sum('Sales Data'[Sales]),ALLSELECTED('Sales Data'[Category])  ) )
  • And the first level (markets)
% level 1 = DIVIDE(sum('Sales Data'[Sales]),CALCULATE(sum('Sales Data'[Sales]),ALLSELECTED('Sales Data'[Market])  ) )

 

As my observation, when showng 1 level in matrix, the parent will be 100% or 1, so I will use IF condition to replace this value to its percentage in the previous level

  • Create measure for Level 2:

 

Level 2 = if([% level 3]=1 , [% level 2],[% level 3])

 

  • Finally, create measure for level 1 with name % group:
% group = if([Level 2]= 1,[% level 1] ,[Level 2])

 

 

For more details, please kindly check my sample file and data for your case.

It's late in Vietnam so i'm going to bed now, hope you take a look with some testing to ensure this is correct for all cases.

 

If this works for you please accept it as solution and also like to give KUDOS.

Best regards
Tri Nguyen

Anonymous's avatar
Anonymous
Not applicable
7 years ago

This solution works except for when the lowest subgroup has a single value.  Do you have any idea how to make it work in that case?  Currently, the percent is of the total of that subgroup calculated over all of the level 2's, rather than that specific level 2.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    Hi,

     

    Share some data and show the expected result.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hello,

       

      Below is a table of sample data:

       

       

       

       

       

       

       

       

       

       

       

       

       

      Following the steps in this post, I created the following measures:

       

           %Activity = DIVIDE(SUM(TableA[Hours]), CALCULATE(SUM(TableA[Hours]), ALLSELECTED(TableA[Activity])))

       

           %Charge = DIVIDE(SUM(TableA[Hours]), CALCULATE(SUM(TableA[Hours]), ALLSELECTED(TableA[Charge])))

       

           %Employee = DIVIDE(SUM(TableA[Hours]), CALCULATE(SUM(TableA[Hours]), ALLSELECTED(TableA[Employee])))

       

           Level1 = IF([%Activity]=1, [%Charge], [%Activity])

       

           MeasureSelect = IF([Level1]=1, [%Employee], [Level1])

       

      The resulting matrix is below:

       

      :

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      Note that where there is a single value at the lowest level (e.g. under Customer Charge), the % is calculating as the % of that value across all employees, rather than for the single employee.  Each one of the lines for Project A should show 100% as it is 100% of the values under Customer Charge.  Non-customer Charges - where there are more than one at the lowest value - are calculating correctly.

       

      Do you have any idea why this is happening and how to fix it?

       

       

      Thank you very much for any help you can provide.

       

      - Stacey

       

       

       

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        7 years ago

        Hi,

         

        Based on the first table that you have shared, please share the exact result you are expecting.  I will help you with writing the DAX formulas.