cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dpbi Regular Visitor
Regular Visitor

RANKX in a measure

Hi all and happy new year.

Need some help with the following -

Table1.PNG

Based on the data in ‘Orders’ table, I need to:

1) calculate InvoiceID margin for each customer

2) for each customer - count the number of occurrences of each margin

3) Summarize the results in the ‘Result Table’

(‘Orders’ table is a single table, not a part of a model).

(‘Result Table’ is a single column table – ‘Margin’ column).

 

For each customer, I’m creating new calculation table (as the ‘CustomerB Calculation’ example above) and then I’m creating a measure to summarize the results of ‘DIFFB’ column and dragged it into ‘Result Table’ visual.

 

My questions:

Is it possible to combine all these steps to a  single measure without the need  of creating new calculation table each time

for each customer?

Is it possible to create the ‘Result Table’ in one step/one measure for all the customers without creating multiple calculation tables ?

 

CustomerB’ table calculations

 

CustomerB Calculation = SUMMARIZECOLUMNS ( Orders [InvoiceID] , FILTER ( Orders , Orders[CustomerID] = "B" ) )

 

Rank = [RankB]   // Calculated column based on the RankB measure

 

RankB = RANKX ( ALL ('CustomerB Calculation') , FIRSTNONBLANK ( 'CustomerB Calculation'[InvoiceID] ,

              'CustomerB Calculation'[InvoiceID] ) , , DESC)

 

DIFFB = VAR CurrentRow = SUM ( 'CustomerB Calculation'[InvoiceID])

              VAR NextRow  = [RankB] + 1

              RETURN

              IF ( [RankB] <> LASTNONBLANK ( ALL ( 'CustomerB Calculation' [Rank] ), 'CustomerB Calculation'[Rank] ),

              CurrentRow -  CALCULATE ( MAX ('CustomerB Calculation'[InvoiceID]  ) , 

                                                           FILTER ( ALL ('CustomerB Calculation') ,[RankB] = NextRow ) ) )

 

The measure CustomerB in the ‘Result Table’ 

CustomerB = COUNTROWS ( FILTER ( 'CustomerB Calculation' , [DIFFB] = SUM ( ResultTable[Margin] ) ) )

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-caliao-msft
Moderator

Re: RANKX in a measure

@dpbi,

 

To achieve your requirement. You can try following method:

1.Create a calculated column in table Orders. Called Rank with following expression:
Rank = RANKX(FILTER(Orders,Orders[CustomerID]=EARLIER(Orders[CustomerID])), Orders[InvoiceID])

We can use EARLIER() function to get previous records in DAX. No need to specify parameters like you did.

 

2.Create another calculated column called DIFFB:
DIFFB = IF(CALCULATE(max(Orders[InvoiceID]),FILTER(Orders,Orders[CustomerID]=EARLIER(Orders[CustomerID]) && Orders[Rank]=EARLIER(Orders[Rank])+1)) = BLANK(),
0,
Orders[InvoiceID]-CALCULATE(MAX(Orders[InvoiceID]),FILTER(Orders,Orders[CustomerID]=EARLIER(Orders[CustomerID]) && Orders[Rank]=EARLIER(Orders[Rank])+1)))

Result shows like this:
Capture1.PNG


3.Create a new table called Result Table, specify column as Margin and give it values as 1 to 10 as you did. Then create a new relationship for Result Table and Orders table:

Capture2.PNG

4.In Result Table, create measures to count the numbers of DIFFB based on Margin and CustomerID. The expression are same for three customers:
Customer A = CALCULATE(COUNT(Orders[DIFFB]),FILTER(Orders,Orders[CustomerID] = "A" && Orders[DIFFB] <> 0))

 

Regards,

Charlie Liao

2 REPLIES 2
Moderator v-caliao-msft
Moderator

Re: RANKX in a measure

@dpbi,

 

To achieve your requirement. You can try following method:

1.Create a calculated column in table Orders. Called Rank with following expression:
Rank = RANKX(FILTER(Orders,Orders[CustomerID]=EARLIER(Orders[CustomerID])), Orders[InvoiceID])

We can use EARLIER() function to get previous records in DAX. No need to specify parameters like you did.

 

2.Create another calculated column called DIFFB:
DIFFB = IF(CALCULATE(max(Orders[InvoiceID]),FILTER(Orders,Orders[CustomerID]=EARLIER(Orders[CustomerID]) && Orders[Rank]=EARLIER(Orders[Rank])+1)) = BLANK(),
0,
Orders[InvoiceID]-CALCULATE(MAX(Orders[InvoiceID]),FILTER(Orders,Orders[CustomerID]=EARLIER(Orders[CustomerID]) && Orders[Rank]=EARLIER(Orders[Rank])+1)))

Result shows like this:
Capture1.PNG


3.Create a new table called Result Table, specify column as Margin and give it values as 1 to 10 as you did. Then create a new relationship for Result Table and Orders table:

Capture2.PNG

4.In Result Table, create measures to count the numbers of DIFFB based on Margin and CustomerID. The expression are same for three customers:
Customer A = CALCULATE(COUNT(Orders[DIFFB]),FILTER(Orders,Orders[CustomerID] = "A" && Orders[DIFFB] <> 0))

 

Regards,

Charlie Liao

dpbi Regular Visitor
Regular Visitor

Re: RANKX in a measure

@v-caliao-msft

 

Thank you very much Charlie for your clear, instructive and detailed explenation. 

Exactly what I needed and it works perfectly.

Absolutely amazing solution.

Your help is greatly appreciated.

 

Thanks and best regards,

Dan

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 345 members 3,538 guests
Please welcome our newest community members: