Forum Discussion
bob57
Helper IV
Helper IVCalculated Column
Hello, I'm hoping that someone can provide me with the DAX to accomplish the following Thank you for your time, Bob Here is the raw data: I need to add the calculated column, Difference: ...
Hi bob57
Here is a sample file with the solution https://we.tl/t-KWLD6ZeAMfDifference = VAR CurrentBase = Data[Base] VAR CurrentRating = Data[Rating] VAR CurrentMatchYesRating = CALCULATE ( MAX ( Data[Rating] ), ALLEXCEPT ( Data, Data[Match] ), Data[Base] = "yes" ) VAR Result = IF ( CurrentBase = "no", CurrentRating - CurrentMatchYesRating, 0 ) RETURN ROUNDDOWN ( Result, 0 )You may need to readjust the rounding type as per your requirement.
bob57Helper IV
Helper IVI did it by hand for demonstration purposes. But the algorithm would go like this:
For each Match (1, 2, and 3) subtract the Base Rating (Rating where Base = yes) from the Rating and round to the nearest whole.
The calculation must be independent for the match so for Match = 2, for example:
when Tee = Red, then 65.4 - 69.7 = -4
when Tee = White, then 69.7 – 69.7 = 0
when Tee = Blue, then 71.3 – 69.7 = 2
I hope this offers the required clarity.
Bob