topic Re: % of Total Calculation with Sub-Categories in DAX Commands and Tips

% of Total Calculation with Sub-Categories

common763 — Tue, 10 May 2022 16:15:32 GMT

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]))))

Re: % of Total Calculation with Sub-Categories

tamerj1 — Tue, 10 May 2022 17:06:05 GMT

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 )

Re: % of Total Calculation with Sub-Categories

Whitewater100 — Tue, 10 May 2022 17:21:24 GMT

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.

Re: % of Total Calculation with Sub-Categories

common763 — Tue, 10 May 2022 17:38:54 GMT

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

Re: % of Total Calculation with Sub-Categories

common763 — Tue, 10 May 2022 20:15:20 GMT

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 )

Re: % of Total Calculation with Sub-Categories

Whitewater100 — Tue, 10 May 2022 20:42:51 GMT

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

Re: % of Total Calculation with Sub-Categories

tamerj1 — Wed, 11 May 2022 03:10:16 GMT

Hi common763

yes you right. Better to use ISINSCOPE

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

Re: % of Total Calculation with Sub-Categories

common763 — Wed, 11 May 2022 12:09:11 GMT

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.

Re: % of Total Calculation with Sub-Categories

common763 — Wed, 11 May 2022 12:31:29 GMT

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 )

Re: % of Total Calculation with Sub-Categories

Whitewater100 — Wed, 11 May 2022 12:46:05 GMT

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!

Re: % of Total Calculation with Sub-Categories

dbodlo — Thu, 16 Jan 2025 18:33:36 GMT

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