Rankx showing correct total but all items in table

[Anonymous]

Hi,

I have a table that ranks sales data. To show the sales in this measure I have created the following measure:

Sales = CALCULATE([Sales],FILTER(VALUES(SalesStaff[BeNr]), IF(RANKX(ALLSELECTED(SalesStaff[BeNr]),[Sales],,DESC)<='Top X'[TopX],[Sales],BLANK())))

When I drag this measure into table with the sales staff, if gives me the correct total. However, it also returns total sales for each row and not only for the Top X values, even though they are no longer part of the total.

Any suggestion how I can fix this?

[Anonymous · Accepted solution]

Hi @Anonymous ,

According to my understand, you want to calculate the sum of sales based on Top X (X is selected in What-if parameter), right?

You could use the following formula:

rank =
RANKX ( ALL ( 'Values' ), CALCULATE ( MAX ( 'Values'[Sales] ) ),, DESC )

sumTopX =
IF (
    [rank] <= SELECTEDVALUE ( 'Top X'[Top X] ),
    CALCULATE (
        SUM ( 'Values'[Sales] ),
        FILTER ( ALL ( 'Values' ), [rank] <= SELECTEDVALUE ( 'Top X'[Top X] ) )
    ),
    BLANK ()
)

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]

Thanks so much for your help @Anonymous !

[amitchandak]

@Anonymous , Something like this

Sales = CALCULATE([Sales],VALUES(SalesStaff[BeNr]), Filter(SalesStaff, IF(RANKX(ALLSELECTED(SalesStaff[BeNr]),[Sales],,DESC)<='Top X'[TopX])))

[Greg_Deckler]

@Anonymous Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.