Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper III

## % of Total Calculation with Sub-Categories

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]))))
3 ACCEPTED SOLUTIONS
Solution Sage

Hi:

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

Super User

yes you right. Better to use ISINSCOPE

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

Solution Sage

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!

9 REPLIES 9
Helper III

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.

Helper III

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 )

Solution Sage

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.

Helper III

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

Solution Sage

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!

Solution Sage

Hi:

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

Super User

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 )``````
Helper III

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 )

Super User

yes you right. Better to use ISINSCOPE

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

## Helpful resources

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors