cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Power_Insight
New Member

Masking/ hiding Unique ID column by rank value

Hi All

Illustrated below is an example of the challenge I am currently facing in Power BI.

 

N.B. This table is ordered in descending order by Price($)

TraderPrice ($)Volume (kg)Unit Price 
ABC Company1,200,000100$           12,000
Go Company900,00090$           10,000
G&E Comapny

700,000

140

$            5,000
Lo Comapny 100,000100$           20,000

 

1. Illustraed above is  an example of a matrix I have manged to create in Power BI.

2. The rows & columns used in the pivot to make this visual are: Trader (row) ; Volume (Value), Price (Value), Unit Price (Value)

3. However, the problem is due to privacy regulations the trader column should be masked in a way that does show what each individual comapny's volume, price and unit price values are. 

4. A rank to replace the company name would therefore  be an ideal solution. 

 

Illustrated below is what we are trying to achieve: 

RankPrice ($)Volume (kg)Unit Price 
11,200,000100$                 12,000
2900,00090$                 10,000
3

700,000

140

$                   5,000
4100,000100$                 20,000

 

We are trying to replace the trader name with a rank

1.  It is important that the rank remains static i.e it should always start from 1  up until the last rank.

2. The reason the rank should always be static is to avoid readers from being able to determine the company by keeping an eye on the rankings as filters are adjusted.

3. Therefore, as filters are manipulated: The rank should either rank Volume, Price or Unit  price from 1 up until the last row. Meaning the Column Rank never changes. but the elements being ranked from Rank 1  to the last rank do.

 

Please kindly advise how best we can resolve this issue!

 

Kind Regards

1 ACCEPTED SOLUTION
smpa01
Super User
Super User

@Power_Insight  you can achieve the rankingColumn like this

 

_rankingColumn = 
RANKX (
    ALL ( 'Table'[Trader] ),
    CALCULATE ( SUM ( 'Table'[Price ($)] ), ALLEXCEPT ( 'Table', 'Table'[Trader] ) ),
    ,
    DESC,
    DENSE
)

 

smpa01_0-1635000955371.png

 

It will calulate the rank upon loading the data based on what is present there

 

smpa01_1-1635001043284.png

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


View solution in original post

5 REPLIES 5
smpa01
Super User
Super User

@Power_Insight  you can achieve the rankingColumn like this

 

_rankingColumn = 
RANKX (
    ALL ( 'Table'[Trader] ),
    CALCULATE ( SUM ( 'Table'[Price ($)] ), ALLEXCEPT ( 'Table', 'Table'[Trader] ) ),
    ,
    DESC,
    DENSE
)

 

smpa01_0-1635000955371.png

 

It will calulate the rank upon loading the data based on what is present there

 

smpa01_1-1635001043284.png

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


View solution in original post

lbendlin
Super User
Super User

This sounds like a standard application of RANKX() in a measure.  What have you tried and where are you stuck?

Hi @lbendlin 

 

Thanks for reaching out. 

 

I have tried using the RANKX() function, however when filters are applied the ranks move with the specific values tied to the Company which makes it possible for readers to track values backwards.

 

The reason this is happeing is the Companies do no necessarily maintain the same ranking for all  kPIs i.e Voulme, Price, Unit price.

 

From my understanding the RANKX() function only applies to a single KPI you are seeking to rank, i.e rank by volume. However,  ranking by volume, price and unit price all at once  would not be possible if the rankings change between each KPI. 

 

EXAMPLE:

N.B. This Matrix is ordered by Volume and The Rank Fucntion has been applied to Volume

Company Volume Price Unit Price 
Rank 11,000,000$ 1,000$ 1,000
Rank 2 500,000$25,000$25
Rank 3100,000$500$200

 

**Keep an eye on rankings in both tables **

 

However, when one applies the matrix filteres and  the matrix is ordered by Price (descending order)  the following happens:

Company Volume Price Unit Price 
Rank 2500,000$ 25,000$ 25
Rank 11,000,000$1,000$1,000
Rank 3100,000$500$200

 As illustrated above since the rank function is tied to the Volume KPI, the rank will not apply to the other metrics thus making it possible to track a specific companies perfromance

- (e.g Rank 1 purchaces the highest volumes, the same comapny ranks 2nd in  terms of priceand their unit price is much higher than competitors)  

- N.B. We are trying to populate these KPIs (Volume, Price, Unit Price) in one matrix table 

 

I hope this helps illustrate the issue we are facing.

 

Thanks!

Thanks Ibendlin 

I will have a look at the link you've sent!  

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors