cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors