topic Re: Percentages at hierarchies in DAX Commands and Tips

Percentages at hierarchies

Anonymous — Wed, 11 Aug 2021 11:22:32 GMT

Hello!

I have the following table

Category	Sub-Category
A	B
A	C
A	C
A	D
Not A	B
Not A	D
A	B
A	B
A	C
A	D

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!

Re: Percentages at hierarchies

amitchandak — Wed, 11 Aug 2021 11:51:28 GMT

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/

Re: Percentages at hierarchies

Jos_Woolley — Wed, 11 Aug 2021 12:19:51 GMT

Hi,

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

Regards

Re: Percentages at hierarchies

Anonymous — Wed, 11 Aug 2021 13:55:39 GMT

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?

Re: Percentages at hierarchies

Anonymous — Wed, 11 Aug 2021 14:28:39 GMT

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

Re: Percentages at hierarchies

Jos_Woolley — Wed, 11 Aug 2021 15:06:27 GMT

Anonymous

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

Regards

Re: Percentages at hierarchies

Anonymous — Wed, 11 Aug 2021 15:21:50 GMT

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

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!

Re: Percentages at hierarchies

Jos_Woolley — Wed, 11 Aug 2021 16:21:18 GMT

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