cancel
Showing results for
Did you mean: Frequent Visitor

## Conditional difference based on conditional values

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 1 ACCEPTED SOLUTION  Super User IV

@wek9294 , 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())

Proud to be a Super User!

3 REPLIES 3  Community Support

Hi @wek9294,

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.  Super User IV

@wek9294 , 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())

Proud to be a Super User!  Super User III

Hi @wek9294

``````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   Announcements #### Welcome to the User Group Public Preview  