Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
German_Chris
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):

German_Chris_0-1654086599823.png


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

German_Chris_1-1654086738517.png


(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
SpartaBI
Community Champion
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(
            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)


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

View solution in original post

3 REPLIES 3
SpartaBI
Community Champion
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 🙂

SpartaBI
Community Champion
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(
            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)


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

@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

German_Chris_0-1654151040336.png


BR German_Chris

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors