Skip to main content
cancel
Showing results for 
Search instead 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

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
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

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