Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@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
@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
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 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!
Thanks Ibendlin
I will have a look at the link you've sent!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |