Calculations by Rank

[PMF999]

I

In my Var column I'd like to calculate the variance between the current rank and the next rank down/lower rank (for each Type). Eg: for Type B (on row 7) rank 4 is 120 less rank 3 (on row 6) is 105 thefore variance = 15.  Many thanks in advance!

[ChrisMendoza · Accepted solution]

@PMF999 -

Would the below work for you?

Var = 
VAR nextRank = Table1[Rank] + 1
VAR differnce = 
    CALCULATE(
        VALUES(Table1[Value]),
        FILTER(ALLEXCEPT(Table1,Table1[Type]),Table1[Rank] = nextRank)
    ) - Table1[Value]
RETURN
    IF(
        differnce >= 0,
        differnce,
        BLANK()
    )

[ChrisMendoza · Accepted solution]

@PMF999 -

The entire code block is your calculated column. Documentation on using VAR can be found @ https://docs.microsoft.com/en-us/dax/var-dax and a nice example article can be found @ https://www.sqlbi.com/articles/variables-in-dax/

[ChrisMendoza · Accepted solution]

@PMF999 -

Would the below work for you?

Var = 
VAR nextRank = Table1[Rank] + 1
VAR differnce = 
    CALCULATE(
        VALUES(Table1[Value]),
        FILTER(ALLEXCEPT(Table1,Table1[Type]),Table1[Rank] = nextRank)
    ) - Table1[Value]
RETURN
    IF(
        differnce >= 0,
        differnce,
        BLANK()
    )

[PMF999]

Hi Chris,

I'm afraid I don't really understand your reply (my fault).

Which bit, exactly, do I need to type into my formula bar?

Many thanks

Peter

[PMF999]

In particular I don'r understand how to feed this expression into the formula;

Var = 
VAR nextRank = Table1[Rank] + 1

Thanks 

[ChrisMendoza · Accepted solution]

@PMF999 -

The entire code block is your calculated column. Documentation on using VAR can be found @ https://docs.microsoft.com/en-us/dax/var-dax and a nice example article can be found @ https://www.sqlbi.com/articles/variables-in-dax/

[PMF999]

Many thanks Chris.

I'll give it a go!

[PMF999]

It almost works Chris.

However this is the result I'd really like;

Primary ID	Type	Date	Value	Rank	Var
Type A20170101	Type A	01/01/17	100	1	0
Type A20170201	Type A	01/02/17	110	2	10
Type A20170301	Type A	01/03/17	125	3	15
Type B20180101	Type B	01/01/18	90	1	0
Type B20180201	Type B	01/02/18	95	2	5
Type B20180301	Type B	01/03/18	105	3	10
Type B20180401	Type B	01/04/18	120	4	15

Where the Var for Rank 1 is alway 0 as there is no lower rank. The var for rank 2 onwards is the value of rank 2 less rank 1, value of rank 3 less rank 2 etc.

Sorry to be a nusiance!

[ChrisMendoza]

@PMF999 -

Var = 
VAR prevRank = Table1[Rank] - 1
VAR differnce = 
    Table1[Value] -
    CALCULATE(
        VALUES(Table1[Value]),
        FILTER(ALLEXCEPT(Table1,Table1[Type]),Table1[Rank] = prevRank )
    )
RETURN
    IF(
        differnce < Table1[Value],
        differnce,
        0
    )

[PMF999]

Here's my code...

= VAR prevRank = Primary_ID[Rank] - 1 VAR differnce = Primary_ID[Value] - CALCULATE( VALUES(Primary_ID[Value], FILTER(ALLEXCEPT(Primary_ID,Primary_ID[Type],Primary_ID[Rank] = prevRank ) ) RETURN IF( differnce < Primary_ID[Value], differnce, 0 )

and here's the error I get...

[ChrisMendoza · Accepted solution]

@PMF999 -

You're missing closing ' ) ' in a couple places.

Copy -> Paste my code and replace 'Table1' with 'Primary_ID' or try formatting multi-line by clicking ALT + ENTER so you can see your opening and closing Parentheses ' ( ' & ' )' respectively a little easier.

[PMF999]

Perfect! Many thanks for perservering with me Chris.

Much appreciated.

I learnt a lot.

Thanks again.

Peter