Ranking users by MTD Revenue, creating slicers of rank groups and adding new measures to the groups

[GvidoB]

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

[v-yulgu-msft · Accepted solution]

Hi @GvidoB,

Please try this:

GTV MTD Last Period2 =
IF ( [GTV MTD Top 10] = BLANK (), BLANK (), [GTV MTD Last Period] )

Regards,

Yuliana Gu

[GvidoB]

Thank you @v-yulgu-msft! This will do.

Gvido