The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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:
Then I drill-down and I will have
Does anyone know how to do this? Thanks!
Solved! Go to Solution.
Hi,
MyMeasure =
DIVIDE (
COUNTROWS ( 'Table' ),
SWITCH (
ISINSCOPE ( 'Table'[Sub-Category] ),
TRUE (), CALCULATE ( COUNTROWS ( 'Table' ), ALL ( 'Table'[Sub-Category] ) ),
COUNTROWS ( ALL ( 'Table' ) )
)
)
Regards
Hi,
MyMeasure =
DIVIDE (
COUNTROWS ( 'Table' ),
SWITCH (
ISINSCOPE ( 'Table'[Sub-Category] ),
TRUE (), CALCULATE ( COUNTROWS ( 'Table' ), ALL ( 'Table'[Sub-Category] ) ),
COUNTROWS ( ALL ( 'Table' ) )
)
)
Regards
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
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)
M2 (50% | 5)
Ideally, my first level of visualization is
Then I drill down to
And finally to
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
@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/
@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
User | Count |
---|---|
27 | |
12 | |
8 | |
8 | |
5 |
User | Count |
---|---|
31 | |
15 | |
12 | |
11 | |
7 |