The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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 |
---|---|
87 | |
84 | |
36 | |
35 | |
30 |
User | Count |
---|---|
95 | |
74 | |
67 | |
52 | |
51 |