Forum Discussion
How to count a rank
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?
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)
3 Replies
- SpartaBI
Community Champion
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)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
- SpartaBI
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 🙂
