Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I'm having some difficulties figuring out how to create a matrix that only shows users (customers) that are ranked and then grouped by their MTD GTV. GTV - gross transaction volume = Revenue.
Table 1 - ' orders'[order_id] / ' orders' [user_id] / ' orders' [value]
Table 2 - datetable
Table 3 - ' users'[user_id] / ' users' [name]
1) I set up a table that I' m using for a slicer:
2) I created GTV MTD Measure:
GTV MTD =
IF(
ISFILTERED('datetable'[Date]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
TOTALMTD(SUM('Orders'[Value]),
'datetable'[Date].[Date])
)
3) I created Measures for all the "Top N" rows:
GTV MTD Top 10 =
CALCULATE( [GTV MTD] ,
filter( values(Users[Name]) ,
rankx ( all( Users[Name]) , [GTV MTD] , , desc ) <=10))
4) Set up the switch of values:
GTV MTD Top N Selection =
Switch(
ALLSELECTED( 'Merchant Groups'[Group]) ,
"Top 10" , [GTV MTD Top 10] ,
"Top 50" , [GTV MTD Top 50] ,
"Top 100" , [GTV MTD Top 100] ,
"Top 200" , [GTV MTD Top 200] ,
"Top 500" , [GTV MTD Top 500] ,
"All" , [GTV MTD]
)
5) Added [GTV MTD Top N Selection] to the matrix and selected Top 10 in the slicer. The result is correct - it shows 10 users from the [GTV MTD Top 10] measure
6) The issue - When I add other measures, for example, [GTV MTD Last Period], it also includes User_Id that are outside the [GTV MTD Top 10] measure.
What can be he done here?
My concern is that Matrix visual filters values by the Column field, and if so, I need to create the Top N measure as a table of 'users'[name] based on the slicer; however, I don't know how to do this.
Any tips?
Gvido
Solved! Go to Solution.
Hi @GvidoB,
Please try this:
GTV MTD Last Period2 = IF ( [GTV MTD Top 10] = BLANK (), BLANK (), [GTV MTD Last Period] )
Regards,
Yuliana Gu
Hi @GvidoB,
Please try this:
GTV MTD Last Period2 = IF ( [GTV MTD Top 10] = BLANK (), BLANK (), [GTV MTD Last Period] )
Regards,
Yuliana Gu
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |