topic Re: How to count a rank in DAX Commands and Tips

How to count a rank

German_Chris — Wed, 01 Jun 2022 12:41:37 GMT

Hello,
I like to count the rank of prices.

I have the following (example) table:

article	price	Account_No	Account_Name	Group1	Group2
A	9	60000	Hans	525A	EPS
A	10	50000	Meier	525A	EPS
A	11	40000	Schulz	525A	EPS
A	12	30000	Friedel	525A	EPS
A	13	20000	Thomas	525A	EPS
B	20	60000	Hans	525A	EPS
B	22	20000	Thomas	525A	EPS
B	10	40000	Schulz	525A	EPS
C	30	50000	Meier	525A	EPS
C	32	60000	Hans	525A	EPS
C	11	40000	Schulz	525A	EPS
D	12	50000	Meier	925A	EPS
D	13	40000	Schulz	925A	EPS
D	10	30000	Friedel	925A	EPS
E	9	20000	Thomas	925A	EPS
E	20	60000	Hans	925A	EPS
E	22	20000	Thomas	925A	EPS
E	10	40000	Schulz	925A	EPS
F	30	50000	Meier	925A	EPS
F	32	60000	Hans	925A	EPS

With the both measures:

Price_:=MAX(Prices_History[price])

Preis_Rank:=RANKX(ALLSELECTED(Prices_History[Account_Name]);[Price_];[Price_];;Dense )

I can analyze the data in a PivotTable like this (works perfekt):

Now, what I need is (a measure) to COUNT the Rank per ACCOUNT and Group1, this should be like this:

(I think) I can't use a calculated column with the rank, because I will use a slicer for account and Group2, so I need a dynamic rank.

Can anybody help with this?

Re: How to count a rank

SpartaBI — Wed, 01 Jun 2022 16:21:17 GMT

German_Chris

STEP 1:
Create this calculated table:

Parater Rank Table = SELECTCOLUMNS( {1,2,3,4,5}, "Rank_Value",[Value] )

STEP 2: Create this measure:
Rank Count =
VAR _current_rank_parameter = SELECTEDVALUE('Parater Rank Table'[Rank_Value])
VAR _result =
COUNTROWS(
     FILTER(
            ADDCOLUMNS(
                      VALUES(Prices_History[article]),
                        "@Rank", [Preis_Rank]
            ),
            [@Rank] = _current_rank_parameter )
)
RETURN IF(ISINSCOPE(Prices_History[Group1]),_result)

STEP 3:
Put the column from the parameter table on the columns of the matrix and the measue in the values (in the rows put what you put in your pic)

Showcase Report – Contoso By SpartaBI

Re: How to count a rank

German_Chris — Thu, 02 Jun 2022 06:31:34 GMT

SpartaBI
many thanks!
For Excel I had to change something.

1. I don´t know how to create a calculated Table in PowerPivot, so I create a "normal" Table an imported it to the model.

2. Function SELECTEDCOLUMNS does not exists in PowerPIvot-Dax, so I replace it with

VAR current_rank_parameter = IF(HASONEVALUE(Rank_Table[Rank_Value]);VALUES(Rank_Table[Rank_Value]))

3. Function ISINSCOPE does not exist in PowerPIvot-Dax, I'ed replace it with HASONEVALUE()

RETURN IF(HASONEVALUE(Prices_History[Group1]); result)

Result is as exspacted

BR German_Chris

Re: How to count a rank

SpartaBI — Thu, 02 Jun 2022 07:39:51 GMT

German_Chris my pleasure 🙂
Hey, check out my showcase report:
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs over there up if you liked it 🙂