RANKX in a measure

[dpbi]

Hi all and happy new year.

Need some help with the following -

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] ) ) )

[v-caliao-msft · Accepted solution]

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

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:

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]

@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