DAX query for rank over multiple columns

[Anonymous]

Hi I have a base table as seen below.

date	name	Value
01-01-2020	A	1
01-01-2020	A	16
01-01-2020	A	4
01-01-2020	B	1
01-01-2020	B	9
01-01-2020	C	19
02-01-2020	A	12
02-01-2020	B	14
02-01-2020	C	7
02-01-2020	C	9
02-01-2020	D	4

I need to rank each name for each date after adding all values for that name for a particular date. Below will be the expected output.

Date	Name	Value	Rank
01-01-2020	A	21	1
01-01-2020	B	10	3
01-01-2020	C	19	2
02-01-2020	A	12	3
02-01-2020	B	14	2
02-01-2020	C	16	1
02-01-2020	D	4	4

I tried using rankx but not able to do. Any idea how to achieve this?

[Greg_Deckler · Accepted solution]

@Anonymous - I did it like this:

Measure 23 = 
    VAR __Date = MAX('Table (23)'[date])
    VAR __Name = MAX('Table (23)'[name])
    VAR __Table =
            SUMMARIZE(
                FILTER(ALL('Table (23)'),[date] = __Date),
                [name],
                "Value",
                SUM([Value])
            )
    VAR __Table1 = ADDCOLUMNS(__Table,"Rank",RANKX(__Table,[Value]))
RETURN
    MAXX(FILTER(__Table1,[name] = __Name),[Rank])

PBIX is attached below sig, you want Page 23, Table (23) and Measure 23.

[amitchandak]

@Anonymous , in case you need measure rank

rankx(all(Table[Name]),calculate(sum(Table[Value])))

In case you need a column or other option refer

https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

[mahoneypat]

Please try this measure expression

Rank =
RANKX ( ALL ( 'Rank'[name] ), CALCULATE ( SUM ( 'Rank'[Value] ) ) )

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Greg_Deckler · Accepted solution]

@Anonymous - I did it like this:

Measure 23 = 
    VAR __Date = MAX('Table (23)'[date])
    VAR __Name = MAX('Table (23)'[name])
    VAR __Table =
            SUMMARIZE(
                FILTER(ALL('Table (23)'),[date] = __Date),
                [name],
                "Value",
                SUM([Value])
            )
    VAR __Table1 = ADDCOLUMNS(__Table,"Rank",RANKX(__Table,[Value]))
RETURN
    MAXX(FILTER(__Table1,[name] = __Name),[Rank])

PBIX is attached below sig, you want Page 23, Table (23) and Measure 23.

[Anonymous]

@Greg_Deckler  - Thanks this worked perfectly fine for me. But when I select multiple dates, it gives rank for each date and not for multiple dates combined. I have a date filter in which user can select more than 1 date and then it does not give desired result. Any idea what tweakwill I have to do in code so that it works for multiple dates as well.

Thanks,

Amit Darak

[Greg_Deckler · Accepted solution]

@Anonymous - I thought you wanted a rank for each date and for each Name like what you showed in the example output you wanted (below). If you don't want that, get rid of the FILTER for date = __Date in your SUMMARIZE

Date	Name	Value	Rank
01-01-2020	A	21	1
01-01-2020	B	10	3
01-01-2020	C	19	2
02-01-2020	A	12	3
02-01-2020	B	14	2
02-01-2020	C	16	1
02-01-2020	D	4	4