Calculate not filtering

[Anonymous]

Hi,

I have the following three tables:

• calendar
• sales
• salesstaff

I built a table, where I try to filter for the Top X sales staff based on a slicer. 

To rank the staff I use the following measure: 

Rank = RANKX(ALLSELECTED(DimSalesStaff),CALCULATE(SUM('Sales'[Sale])),,DESC,Dense)

To then show the revenue on for the Top X staff, I use the following measure:

Sales = 
var TopX = SELECTEDVALUE('Top X'[Top X])
Return
CALCULATE(SUM('Sales'[Sale]),FILTER('Sales', [Rank]<= 10))

However, the rank filter is not working. Any suggestion on how I can fix this?

Thanks

[Anonymous]

Hi @Anonymous ,

It seems like you want to calculate the sum of Sales based on dynamic  slicer value for Top N.

You could use the following formula:

Rank =
RANKX (
    ALLSELECTED ( Sales ),
    CALCULATE ( SUM ( 'Sales'[Sale] ) ),
    ,
    DESC,
    DENSE
)

filterTopN =
VAR _select = MAX ( forSlicer[value] ) RETURN IF ( [Rank] <= _select, 1, 0 )

total =
VAR _select =
    MAX ( forSlicer[value] )
RETURN
    CALCULATE ( SUM ( Sales[Sale] ), FILTER ( ALL ( Sales ), [Rank] <= _select ) )

My visualization looks like this:

Is the result what you want? If you have any questions, please upload some data samples and expected output.

Please do mask sensitive data before uploading.

Best Regards,

Eyelyn Qin

[Anonymous]

@Anonymous @Pragati11 Thanks for your feedback.

However, neither of your solutions fixed my problem. I have attached a link to test file. Could you please have a look at this and tell me why my sales measure is not working correctly?

Thanks!

[Pragati11 · Accepted solution]

HI @Anonymous ,

Can you please refer following blog that I wrote some time back to display number of selected rows in a table for a selected Category on Total sales:

https://community.powerbi.com/t5/Community-Blog/Controlling-Number-of-Rows-to-Be-Displayed-in-a-Table-Visual/ba-p/1317661

I think this is what you are looking for.

Let me know if this helps.

Thanks,

Pragati

[Anonymous]

Thanks for sharing @Pragati11 This is very helpful!

[Pragati11]

Hi @Anonymous ,

Can you try modifying your RANKX calculation as follows:

TotalSales = CALCULATE(SUM('Sales'[Sale]))

Rank = RANKX(ALLSELECTED(DimSalesStaff),TotalSales,,DESC,Dense)

As you are creating a ranking measure, try above way and see if the ranks work now.

Also, share screesnhot around what ranks you are getting.

Thanks,

Pragati