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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Percentages at hierarchies

Hello!

 

I have the following table

  

CategorySub-Category
AB
AC
AC
AD
Not AB
Not A D
AB
AB
AC
AD

 

I want to create a Stacked Bar Chart that allows me to see how many time each Sub-Category appears in a Category:

 

In a first level i will have: 

  • A -  80% 
  • Not A  - 20%

 

Then I drill-down and I will have 

  • A
    • B - 37,5%
    • C - 37,5%
    • D - 25%
  • Not A
    • B - 50%
    • D- 50%

 

Does anyone know how to do this? Thanks!

1 ACCEPTED SOLUTION
Jos_Woolley
Solution Sage
Solution Sage

Hi,

MyMeasure =
DIVIDE (
    COUNTROWS ( 'Table' ),
    SWITCH (
        ISINSCOPE ( 'Table'[Sub-Category] ),
        TRUE (), CALCULATE ( COUNTROWS ( 'Table' ), ALL ( 'Table'[Sub-Category] ) ),
        COUNTROWS ( ALL ( 'Table' ) )
    )
)

Regards

View solution in original post

7 REPLIES 7
Jos_Woolley
Solution Sage
Solution Sage

Hi,

MyMeasure =
DIVIDE (
    COUNTROWS ( 'Table' ),
    SWITCH (
        ISINSCOPE ( 'Table'[Sub-Category] ),
        TRUE (), CALCULATE ( COUNTROWS ( 'Table' ), ALL ( 'Table'[Sub-Category] ) ),
        COUNTROWS ( ALL ( 'Table' ) )
    )
)

Regards

Anonymous
Not applicable

Btw, @Jos_Woolley how do I get the absolute value of each percentage? So I can put but measure in my visualization.

 

And how do I change the % formula if I want to put another level above Category? 

@Anonymous 

Can you clarify your two new requirements with an updated example together with some expected results?

Regards

Anonymous
Not applicable

Sure!

 

Main Category | Category | Sub-Category| ABABACAD

----------------------------------------------

M1                   |      A         |              B

M2                   |      A         |              C

M1                   |      A         |              C

M2                   |      A         |              D

M1                   |     Not A   |              B

M2                   |     Not A   |              D

M1                   |      A         |              B

M2                   |      A         |              B

M1                   |      A         |              C

M2                   |      Not A  |              D

 

 

Now I have an upper level

M1 (50% | 5)

  • A (80% | 4)
    • B  (50% | 2)
    • C (50% | 2)
  • Not A (20% | 1)
    • B (100% | 1)
    • C
    • D

M2 (50% | 5)

  • A ( 60% | 3)
    • B (33% | 1)
    • C (33% | 1)
    • D (33% | 1)
  • Not A (40% | 2)
    • B (50% | 1)
    • C
    • D (50% | 1)

 

Ideally, my first level of visualization is

  • M1
  • M2

Then I drill down to

  • M1 - A
  • M1 - Not A
  • M2 - A
  • M2 - Not A

And finally to 

  • M1 - A - B
  • M1 - A - C
  • M1 - A - D
  • M1 - Not A - B
  • M1 - Not A - C
  • M1 - Not A - D
  • M2 - A - B
  • M2 - A - C
  • M2 - A - D
  • M2 - Not A - B
  • M2 - Not A - C
  • M2 - Not A - D

 

with respective percentages and absolute values.

 

Thanks a lot!

Thanks a lot!

MyMeasureValue =
COUNTROWS ( 'Table' )
MyMeasure% =
DIVIDE (
    [MyMeasureValue],
    SWITCH (
        TRUE (),
        ISINSCOPE ( 'Table'[Sub-Category] ), CALCULATE ( [MyMeasureValue], ALL ( 'Table'[Sub-Category] ) ),
        ISINSCOPE ( 'Table'[Category] ), CALCULATE ( [MyMeasureValue], ALL ( 'Table'[Category] ) ),
        COUNTROWS ( ALL ( 'Table' ) )
    )
)

Regards

amitchandak
Super User
Super User

@Anonymous , with help from isinscope, a measure like 


new measure =
var _1 = divide(countrows(Table), calculate(countrows(Table), allselected(Table)))
var _2 = divide(countrows(Table), calculate(countrows(Table), filter(allselected(Table), [Category] = max([Category]))))
return
if(not(isinscope(table[Sub-Category])), _2,_1)

 

refer if needed

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

@amitchandak I got your solution, thank you! What if I want to add another level in my hierarchy: let's say above category I add Main Category (so, Main Category -> Category -> Sub-Category). I do I change and keep the same logic? 

 

And how to I get the absolute values to add to the visualization? 

 

Thanks

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

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