Create a ranking for sensitive data

[fabiocovre]

Hello,

I'm working with medical data, and due that, I cannot show the name of the people in my report.

I have this following table in my dashboard:

Name - Cost

John - 50,000

Mark - 20,000

Peter - 10,000

And I need to change the collumn "Name" for a collumn called "Ranking". Example below:

Ranking - Cost

1 - 50,000

2 - 20,000

3 - 10,000

I tried to do this formula "Ranking = RANKX(ALLSELECTED('_BASE'[Name]);[SumCost])", but when I replace the "Name" field in my table for "Ranking", this is the result.

Ranking - Cost

1 - 80,000

Is it possible to be done?

Thank you!

[Greg_Deckler]

If it is truly a calculated column in a table then it should just be:

Ranking = RANKX('BASE',[Cost])

[fabiocovre]

Sorry, I forgot to detail it, bit my database has many values for the same users. I would have to sum it... Is it possible to use this calculated column anyway?

The rank also should change according to the filters. Also showing 1st, 2nd, 3rd etc...

Thank you!

[v-yuta-msft]

Hi fabiocovre,

Try DAX below:

Rank =
RANKX (
    ALLSELECTED ( Table1 ),
    CALCULATE ( SUM ( Table1[Cost] ), ALLEXCEPT ( Table1, Table1[Name] ) ),
    ,
    DESC,
    DENSE
)

 

PBIX here: https://www.dropbox.com/s/utytv44dtowsqh0/Create%20a%20ranking%20for%20sensitive%20data.pbix?dl=0

Regards,

Jimmy Tao

[fabiocovre]

v-yuta-msft,

Sorry, I need a table with just the rank and the total cost

In your example, the table would be:

Rank - SumCost

1 - 101515

2 - 71515

3 - 45423

The real name can't be shown in the table.

Thank you for the help.

[v-yuta-msft · Accepted solution]

Hi fabiocovre,

Create a calculate column using DAX like below:

Total Cost = CALCULATE(SUM(Table1[Cost]), FILTER(Table1, Table1[Name] = EARLIER(Table1[Name])))

Then modify the rank measure like this:

Rank = RANKX(ALLSELECTED(Table1), CALCULATE(MAX(Table1[Total Cost]), ALLEXCEPT(Table1, Table1[Name])), , DESC, Dense)

   

In addtion, I'm afraid the Name column can't be removed because allselect() function are based on the Name column.

PBIX here: https://www.dropbox.com/s/utytv44dtowsqh0/Create%20a%20ranking%20for%20sensitive%20data.pbix?dl=0

Regards,

Jimmy Tao