Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
@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())
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
@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())
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
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |