cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
common763
Helper II
Helper II

% 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

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 

 

Whitewater100_0-1652215357307.png

 

View solution in original post

tamerj1
Community Champion
Community Champion

Hi @common763 

yes you right. Better to use ISINSCOPE

 

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

View solution in original post

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!

View solution in original post

9 REPLIES 9
common763
Helper II
Helper II

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. 

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
Solution Sage
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.

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

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!

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 

 

Whitewater100_0-1652215357307.png

 

tamerj1
Community Champion
Community Champion

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 )

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
Community Champion
Community Champion

Hi @common763 

yes you right. Better to use ISINSCOPE

 

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

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors