% of Total Calculation with Sub-Categories

[common763]

Feel like I should know this but I am having an issue and need some help.

I want to calculate the % from Total in a report and have it calculate for both the main, and sub-level categories I have in the rows.  I know why my current formula isnt working but need some help on how to fix.  I want it to calculate from the total for both sub and main categories.  If someone can lead me in the right direction it would be greatly appreciated.  Maybe I am not even using the best formula to do this.  Thanks. 

Info below. 

First formula will calculate the percentage correctly for the main category, but the sub displays 100% for every value.

% of Total =  (CALCULATE(sum([Issues Reported]))/(calculate(sum([Issues Reported]),ALL(TABLE1[MAIN CATEGORY]))))

This one obviously does the opposite, calculating the % from the sub and leaving main with 100%

% of Total =  (CALCULATE(sum([Issues Reported]))/(calculate(sum([Issues Reported]),ALL(TABLE1[MINOR CATEGORY]))))

[Whitewater100 · Accepted solution]

Hi:

Here are two different ways to solve for your question. Please see attached, using my data to make example.

https://drive.google.com/file/d/1J9Pf910SKSDzcDkHNu48_OM4JpzNcstd/view?usp=sharing 

[tamerj1 · Accepted solution]

Hi @common763 

yes you right. Better to use ISINSCOPE

IF (ISINSCOPE ( TABLE1[MINOR CATEGORY] ), PercentSubCat, PercentCat )

[dbodlo]

Hi everyone!

thanks for your help!

he i can improve the code to use with multiple levels!!

%_of_Total_hierarchies =
VAR item_level_1 =
    SUM(monthly_sales[units])
        / CALCULATE(SUM(monthly_sales[units]), ALL(items[item_level_1]))
VAR item_level_2 =
    SUM(monthly_sales[units])
        / CALCULATE(SUM(monthly_sales[units]), ALL(items[item_level_2]))
VAR item_level_3 =
    SUM(monthly_sales[units])
        / CALCULATE(SUM(monthly_sales[units]), ALL(items[item_level_3]))
VAR item_level_4 =
    SUM(monthly_sales[units])
        / CALCULATE(SUM(monthly_sales[units]), ALL(items[item_level_4]))
RETURN
    SWITCH(
        TRUE(),
        ISINSCOPE(items[item_level_4]), item_level_4,
        ISINSCOPE(items[item_level_3]), item_level_3,
        ISINSCOPE(items[item_level_2]), item_level_2,
        ISINSCOPE(items[item_level_1]), item_level_1,
        BLANK()
    )

i hope be useful

daniel

[common763]

Whitewater100-I will take a look at this now.  Thanks for the post.

Tamerj1-Thanks also for your response.  WIll take some time with both suggestions and then post when it works correctly.  Thanks. 

[common763]

Ok so Whitewater100 I plan to go through your pbix file today and get some understanding of how all of this works so thanks so much for that.  Tamerj1 I just tweaked the last part and it works perfectly so also thanks.  Here is working solution for others who may have the same issue.  

% of Total =
VAR PercentCat =
SUM ( TABLE1[Issues Reported] )
/ CALCULATE ( SUM ( TABLE1[Issues Reported] ), ALL ( TABLE1[MAIN CATEGORY] ) )
VAR PercentSubCat =
SUM ( TABLE1[Issues Reported] )
/ CALCULATE ( SUM ( TABLE1[Issues Reported] ), ALL ( TABLE1[MINOR CATEGORY] ) )
RETURN
IF ( ISINSCOPE ( TABLE1[MINOR CATEGORY] ), PercentSubCat, PercentCat )

[Whitewater100]

Hi:

You can try this patttern:

* It would be suggested you have a dimension table(say "Issues" for Cat and Sub-Cat.  Then where it says TABLE1[Main Category} or TABLE1[Minor Category] the measure below would be replaced by Issues[Category] or Issues[Sub Cat]

Create base measure 
Total Amt = SUM([Issues REported])

MEASURE Sales[Pct over parent] =

        VAR _All =

            CALCULATE ([Total Amt]), ALLSELECTED () )

        VAR Category =

            CALCULATE ( [Total Amt], ALLSELECTED (), VALUES ( TABLE1[MAIN CATEGORY] ) )

        VAR Current = [Total Amt]

        RETURN

            SWITCH (

                TRUE (),

                ISINSCOPE ( TABLE1[MINOR CATEGORY] ), DIVIDE ( Current, Category ),

                ISINSCOPE ( TABLE1[MAJOR CATEGORY] ),    DIVIDE ( Current, _All )

            )

This should show the percent of total for each Major and how the how the sub-cat builds to the category.

I hope this is what you are looking for.

[common763]

Thanks for taking the time with this.  I will try and get this to work then come back and comment.  Appreciate it. 

[Whitewater100 · Accepted solution]

You are welcome. You'll notice one way is taking each line (whether Cat or Sub Cat) and showing what % of total each individual line is and the other way is showing the Categories equaling the 100% and then a separate build of sub-categories reporting to a Category, with those sub cat's equaling a 100% of each individual category. Have a good one!

[Whitewater100 · Accepted solution]

Hi:

Here are two different ways to solve for your question. Please see attached, using my data to make example.

https://drive.google.com/file/d/1J9Pf910SKSDzcDkHNu48_OM4JpzNcstd/view?usp=sharing 

[tamerj1]

Hi @common763 

you can use 

% of Total =
VAR PercentCat =
    SUM ( TABLE1[Issues Reported] )
        / CALCULATE ( SUM ( TABLE1[Issues Reported] ), ALL ( TABLE1[MAIN CATEGORY] ) )
VAR PercentSubCat =
    SUM ( TABLE1[Issues Reported] )
        / CALCULATE ( SUM ( TABLE1[Issues Reported] ), ALL ( TABLE1[MINOR CATEGORY] ) )
RETURN
    IF ( HASONEVALUE ( TABLE1[MINOR CATEGORY] ), PercentSubCat, PercentCat )

[common763]

Just noticed something.  I opted for the second solution and it is doing one thing that needs correcting.   If there is one value in the sub-category it is displaying the percentage in the main category.  Otherwise it is doing everything correctly.  How do I tweak the below formula to display all regardless.  That would include the % of total of Main, and the sub %.  That HASONEVALUE is just putting 100% in the main category for rows with just one sub. 

RETURN
IF ( HASONEVALUE ( TABLE1[MINOR CATEGORY] ), PercentSubCat, PercentCat )

[tamerj1 · Accepted solution]

Hi @common763 

yes you right. Better to use ISINSCOPE

IF (ISINSCOPE ( TABLE1[MINOR CATEGORY] ), PercentSubCat, PercentCat )