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.
Profit Rank by Industry =
VAR ThisGroup = '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())
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?
