Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Power_Insight
Regular Visitor

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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors