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.
I spent far too long on this, and I'm admittedly no expert. I need some major help using RANKX to rank net profit within each industry, based on whatever month is selected in the date slicer. I've successfully ranked the profit by group, but the slicer has no effect on the ranking. The slicer does correctly recalculate the Net Profit for the single month or multiple months selected, but the ranking only ranks the latest month.
I have 3 tables with relationships:
A date table: Below is a similar model. The actual table goes on for years, dating back to 2018 up to current date.
Date | Key |
1/1/2022 | 01-2022 |
1/2/2022 | 01-2022 |
1/3/2022 | 01-2022 |
1/4/2022 | 01-2022 |
1/5/2022 | 01-2022 |
1/6/2022 | 01-2022 |
1/7/2022 | 01-2022 |
1/8/2022 | 01-2022 |
1/9/2022 | 01-2022 |
1/10/2022 | 01-2022 |
1/11/2022 | 01-2022 |
1/12/2022 | 01-2022 |
1/13/2022 | 01-2022 |
1/14/2022 | 01-2022 |
1/15/2022 | 01-2022 |
1/16/2022 | 01-2022 |
1/17/2022 | 01-2022 |
1/18/2022 | 01-2022 |
1/19/2022 | 01-2022 |
1/20/2022 | 01-2022 |
1/21/2022 | 01-2022 |
1/22/2022 | 01-2022 |
1/23/2022 | 01-2022 |
1/24/2022 | 01-2022 |
1/25/2022 | 01-2022 |
1/26/2022 | 01-2022 |
1/27/2022 | 01-2022 |
Our Dealer Summary, which is updated daily. Table actually holds about 250 columns of data, but the two columns that are relevant are below.
Dealer ID | Industry |
1 | Auto |
2 | Auto |
3 | Auto |
4 | Auto |
5 | Auto |
6 | Auto |
7 | Furniture |
8 | Furniture |
9 | Furniture |
10 | Furniture |
11 | Furniture |
12 | Furniture |
13 | Furniture |
14 | Furniture |
And a YTD Net Profit table, provided to me monthly. The Net Profit % is a measure.
Key | Dealer ID | Revenue | Net Profit | Net Profit % |
01-2022 | 1 | $ 23,000 | $ 12,000 | 52% |
02-2022 | 1 | $ 25,000 | $ 11,000 | 44% |
03-2022 | 1 | $ 30,000 | $ 15,000 | 50% |
04-2022 | 1 | $ 35,000 | $ 17,000 | 49% |
05-2022 | 1 | $ 28,000 | $ 12,000 | 43% |
06-2022 | 1 | $ 40,000 | $ 23,000 | 58% |
07-2022 | 1 | $ 50,000 | $ 23,000 | 46% |
01-2022 | 2 | $ 240,000 | $ 100,000 | 42% |
02-2022 | 2 | $ 250,000 | $ 230,000 | 92% |
03-2022 | 2 | $ 230,000 | $ 120,000 | 52% |
04-2022 | 2 | $ 235,000 | $ 150,000 | 64% |
05-2022 | 2 | $ 450,000 | $ 250,000 | 56% |
06-2022 | 2 | $ 230,000 | $ 100,000 | 43% |
07-2022 | 2 | $ 310,000 | $ 160,000 | 52% |
The Dealer ID is on the YTD Net Profit is related to the Dealer Summary Dealer ID.
The Key on the YTD Net Profit is related to the Date Table's Key.
I've created the following calculated column.
The slicer uses the month from the Date column of the Date Table. How can I get the formula to use the recalculated net profit to re-rank, based on the slicer?
@sharpcm01 , create measure not a column.
Profit Rank by Industry =
VAR ThisGroup = Max('Dealer Summary'[Industry])
RETURN
if(not(ISBLANK('YTD Net Profit'[Net Profit %])),
RANKX(
FILTER(
ALLSELECTED('Dealer Summary'),
'Dealer Summary'[Industry] = ThisGroup
),
'YTD Net Profit'[Net Profit %],
,
,
Skip
),BLANK())
In case month year is part of the visual, rank will get distributed inside that one
explained here Power BI Rank Across dimension tables: https://youtu.be/X59qp5gfQoA
Adding max doesn't work, and I'm left with 4 distinct values across 22,000 or so lines and 20 or so industries.
Adding max doesn't work, and I'm left with 4 distinct values across 22,000 or so lines and 20 or so industries.
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |