Conditional difference based on conditional values

[Anonymous]

Below is my data

What I'm trying to get is the cost difference from the rank 1 carrier against lines that don't have a rank.  So for line 2, it would calculate 150 - 100, and line 5 would calculate 900 - 100. My data would look like.

Alternatively, it can look like this since I can filter out the non-blanks

[Anonymous]

Hi @Anonymous,

I think the above expressions are suitable for your requirement. If they meet your requirement, you can consider accepting these as a solution to help others who have similar requirements to find it more quickly.

If not, you can feel free to post here with detailed descriptions.

Regards,
Xiaoxin Sheng

[amitchandak · Accepted solution]

@Anonymous , Try as a new column

new column =
var _min = minx(filter(Table, [lane] =earlier([lane]) && [Rank]=1),[Cost])
return
if(isblank([RANK]) || [RANK]<>1 , [Cost] -_min, blank())

Or

new column =
var _min = minx(filter(Table, [lane] =earlier([lane]) && [Rank]=1),[Cost])
return
if(isblank([RANK])  , [Cost] -_min, blank())

[AlB]

Hi @Anonymous

Cost from non-rank =
VAR rank1Cost_ =
    CALCULATE (
        DISTINCT ( Table1[Cost] ),
        Table1[Rank] = 1,
        ALLEXCEPT ( Table1, Table1[Lane] )
    )
RETURN
    IF ( NOT Table1[Rank] IN { 1, BLANK () }, Table1[Cost] - rank1Cost_ )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers