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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
pbv
New Member

Calculating percentage of a subcategory related to total count of the total category (per week)

Hi,

 

I am looking for some help regarding a bar chart that shows the percentage of the count of a type of subcategory (selected in my filter) based on the total number of a category specified in time. Basically the data I have looks like this:

 

CategoryWeek Subcategory
A1x
A2y
A2x
A2z
A2y
A2y
A3x
A3z
A3x
B3x
B3y
C1z

 

And I want to apply the following filters:

Filter on CategoryA
Filter on Subcategoryx
Filter on week[All]

 

After which I want to show the percentage of counted "x" relative to the total count of "A" per week, i.e. a result (in a bar graph that basically shows:

 

CategorySubcategoryWeek% of Category
Ax1100%
  220%
  367%

 

So far, my attempts have not gotten me far (not very experienced yet) and when using metrics to calculate totals and percentages, the Category "A" changes with the filter "x". In other words I keep getting 100% as an outcome in the end. Could someone help me with this?

 

Thanks a lot!

1 ACCEPTED SOLUTION
Zubair_Muhammad
Community Champion
Community Champion

@pbv

 

Try this MEASURE

 

% of category =
DIVIDE (
    COUNT ( TableName[Subcategory] ),
    CALCULATE (
        COUNT ( TableName[Subcategory] ),
        ALLEXCEPT ( TableName, TableName[Category], TableName[Week ] )
    )
)

Regards
Zubair

Please try my custom visuals

View solution in original post

7 REPLIES 7
GILLOJUNKUMAR
Frequent Visitor

Hi, 

This is good, it is working in matrix table, but not working for bar chart.... here is the scenario:

 

i have data of around 10k records with consists of name, months, multiple subcategories (poor, good, very good, excellent) in column1

 

My question is i want to display month wise % of good, poor, very good, excellenet in bar chart.. i wnat to display & on each bar.

 

Hi,

Share data in a format that can b pasted in an MS Excel file.  Ensure that there is also a Year column (along with Month column) in your dataset.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

Hi,

 

Try this measure

 

=COUNTROWS(Table1)/CALCULATE(COUNTROWS(Table1),ALL(Table1[Subcategory]))

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur

 

Thanks, for my specific question this works as well.

 

Difference with the solution of @Zubair_Muhammad is that with that solution I can select multiple filters (i.e. a second category related to the above data), while keeping the "base value" the same (i.e. the number of "A"'s). With your solution it is possible to select an additional filter, which than becomes the new "base value". 

Zubair_Muhammad
Community Champion
Community Champion

@pbv

 

Try this MEASURE

 

% of category =
DIVIDE (
    COUNT ( TableName[Subcategory] ),
    CALCULATE (
        COUNT ( TableName[Subcategory] ),
        ALLEXCEPT ( TableName, TableName[Category], TableName[Week ] )
    )
)

Regards
Zubair

Please try my custom visuals

@pbv

 

But i get different results wth your sample data Smiley Surprised

 

percent.png

 

 


Regards
Zubair

Please try my custom visuals

My percentages were atually wrong, I will change it in the original answer, solutions works for me!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors