Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hoping someone might be able to point me in the right direction ...
I'm trying to produce a matrix that displays the sum of a selected value (from slicer) with subsequent rows showing the sum of 'all' (whole country) and the sum of 'Type' associated with the selection. I've tried to enact a solution posted online and this works well for the selected value and a 'whole country' national total but struggling to show the sum of the 'type' for the selection. Likely going about this in the wrong way, any pointers appreciated! The data format below and workings in this Pbix.
What i'm trying to achieve:
Measures:
Total = Sum(Fact[Count])
Total Count =
VAR x = SELECTEDVALUE('Rows'[Value])
RETURN
SWITCH(TRUE(),
x = "Whole Country", [Total](REMOVEFILTERS()),
x = "Selected",[Total](REMOVEFILTERS(), TREATAS(VALUES('Selector'[Name]), Fact[Name])),
// x = "Type", ??? not sure how to get this part working //
BLANK()
)
Fact Table:
id | Count | Name |
1 | 223 | ceddar |
2 | 456 | cherry |
3 | 123 | plumb |
4 | 904 | blossom |
Dimensions Table:
id | Name | Type |
1 | ceddar | 2 |
2 | cherry | 2 |
3 | plumb | 1 |
4 | blossom | 3 |
Solved! Go to Solution.
@Lebowski I have so many questions about this but here is your answer. PBIX is attached below signature. First, I have no idea why your made your slicer separate as a disconnected table. Just makes the DAX harder. But, seriously, where on earth did you learn that absolutely bizarre, freakish, and heinous DAX notation? Please, for all that is good and holy, don't write your DAX that way I beg you.
Total Count 1 =
VAR x = SELECTEDVALUE('Rows'[Value])
VAR __selector = MAX( 'Selector'[Name])
VAR __type = MAXX(FILTER( 'Dimensions', [Name] = __selector ), 'Dimensions'[Type])
VAR __result = SWITCH(TRUE(),
x = "Whole Country", [Total](REMOVEFILTERS()),
x = "Selected",[Total](REMOVEFILTERS(), TREATAS(VALUES('Selector'[Name]), Fact[Name])),
// x = "Type", ??? not sure how to get this part working //
SUMX( FILTER( ALL('Fact'), [id] IN SELECTCOLUMNS( FILTER( ALL('Dimensions'), [Type] = __type ), "id", [id] ) ), [Count] )
)
RETURN
__result
@Lebowski I have so many questions about this but here is your answer. PBIX is attached below signature. First, I have no idea why your made your slicer separate as a disconnected table. Just makes the DAX harder. But, seriously, where on earth did you learn that absolutely bizarre, freakish, and heinous DAX notation? Please, for all that is good and holy, don't write your DAX that way I beg you.
Total Count 1 =
VAR x = SELECTEDVALUE('Rows'[Value])
VAR __selector = MAX( 'Selector'[Name])
VAR __type = MAXX(FILTER( 'Dimensions', [Name] = __selector ), 'Dimensions'[Type])
VAR __result = SWITCH(TRUE(),
x = "Whole Country", [Total](REMOVEFILTERS()),
x = "Selected",[Total](REMOVEFILTERS(), TREATAS(VALUES('Selector'[Name]), Fact[Name])),
// x = "Type", ??? not sure how to get this part working //
SUMX( FILTER( ALL('Fact'), [id] IN SELECTCOLUMNS( FILTER( ALL('Dimensions'), [Type] = __type ), "id", [id] ) ), [Count] )
)
RETURN
__result
User | Count |
---|---|
77 | |
76 | |
43 | |
30 | |
26 |
User | Count |
---|---|
97 | |
91 | |
52 | |
47 | |
46 |