Power BI Rank Based on Day on Day Change.

[Jmash]

Hi All,

How to frame a logic for ranking dataset based on a category and its DoD % Chnage for the below example. Table below shows DoD% increase Change Column. I want to rank them from 1 to 100 based on daily movement by country in Power BI Dax.

Date	Country	Balance USD	DoD % Change	Rank Based on DoD Change
3/01/2020	A	56	81%	1
7/01/2020	A	256	64%	2
4/01/2020	A	87	55%	3
2/01/2020	A	31	55%	4
6/01/2020	A	156	47%	5
8/01/2020	A	366	43%	6
4/01/2020	B	50	43%	7
3/01/2020	B	35	40%	8
5/01/2020	B	70	40%	9
6/01/2020	B	95	36%	10
7/01/2020	B	125	32%	11
11/01/2020	A	656	29%	12
8/01/2020	B	160	28%	13
9/01/2020	A	458	25%	14
2/01/2020	B	25	25%	15
9/01/2020	B	200	25%	16
10/01/2020	B	245	23%	17
5/01/2020	A	106	22%	18
11/01/2020	B	295	20%	19
12/01/2020	A	784	20%	20
12/01/2020	B	350	19%	21
13/01/2020	B	410	17%	22
14/01/2020	B	475	16%	23
10/01/2020	A	510	11%	24
13/01/2020	A	856	9%	25
14/01/2020	A	925	8%	26
1/01/2020	A	20		27
1/01/2020	B	20		27

[v-yangliu-msft · Accepted solution]

Hi  @Jmash ,

If you want to sort [DoD % Change] by grouping by [Date] and [Country], you can try the following dax:

Measure =
RANKX(
    FILTER(ALLSELECTED('Table'),   'Table'[Date]=MAX('Table'[Date])&&'Table'[Country]=MAX('Table'[Country])),CALCULATE(SUM('Table'[DoD % Change])),, DESC)

If you want to implement [Rank Based on DoD Change] to do a sort on all, you can try the following dax:

Measure 2 =
RANKX(
    ALLSELECTED('Table'),
    CALCULATE(SUM('Table'[DoD % Change])),,DESC)

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Jmash , You can have date table and create DOD using Time Intelligence

example

This Day = CALCULATE(SUM(Table[Value]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))

Last Day = CALCULATE(SUM(Table[Value]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))

Last Day = CALCULATE(SUM(Table[Value]), previousday('Date'[Date]))

DOD = divide([This Day]- [Last Day], [Last Day])

and you can create rank post that

Rankx(allselected(Table[Date]), [DOD])

Day Intelligence - Last day, last non continous day
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...

Measure Rank: https://www.youtube.com/watch?v=DZb_6j6WuZ0&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=40
https://www.youtube.com/watch?v=cN8AO3_vmlY&t=25627s

Power BI - New DAX Function: RANK - How It Differs from RANKX: https://youtu.be/TjGkF44VtDo