cancel
Showing results for
Did you mean:
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(\$)

 Trader Price (\$) Volume (kg) Unit Price ABC Company 1,200,000 100 \$           12,000 Go Company 900,000 90 \$           10,000 G&E Comapny 700,000 140 \$            5,000 Lo Comapny 100,000 100 \$           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:

 Rank Price (\$) Volume (kg) Unit Price 1 1,200,000 100 \$                 12,000 2 900,000 90 \$                 10,000 3 700,000 140 \$                   5,000 4 100,000 100 \$                 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
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
)``````

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

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

Proud to be a Super User!

New Animated Dashboard: Sales Calendar

5 REPLIES 5
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
)``````

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

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

Proud to be a Super User!

New Animated Dashboard: Sales Calendar

Super User

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

New Member

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 1 1,000,000 \$ 1,000 \$ 1,000 Rank 2 500,000 \$25,000 \$25 Rank 3 100,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 2 500,000 \$ 25,000 \$ 25 Rank 1 1,000,000 \$1,000 \$1,000 Rank 3 100,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!

Super User
New Member

Thanks Ibendlin

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

Announcements