Percentages at hierarchies

[Anonymous]

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!

[Jos_Woolley · Accepted solution]

Hi,

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

Regards

[Anonymous]

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? 

[Jos_Woolley]

@Anonymous 

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

Regards

[Anonymous]

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

• 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!

[Jos_Woolley]

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]

@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/

[Anonymous]

@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