cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## 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?

1 ACCEPTED SOLUTION
Community Champion

@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(
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

3 REPLIES 3
Community Champion

@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 🙂

Community Champion

@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(
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

New Member

@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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors