Forum Discussion
Differential for specific values
hi v-ljerr-msft,
thank you for your feedback....
i've found a solution at the end wich is: calculating every min AIF for each CXR and then making measures QF-CXR in order to highlight differentials.
This sounds like a job for the EARLIER() Function. This function allows you to create calculated columns that can compare/summarize values using values from other rows. Honestly, it's hard to explain EARLIER() but it works, so the best way to learn is by example and just watching the magic happen. For your scenario, if you wanted to sum all of Qantas' AIF, you could do that with the following syntax/steps:
Total AIF = CALCULATE(SUM([AIF]),FILTER('TABLE NAME','TABLE NAME'[CXR]=EARLIER('TABLE NAME'[CXR])))
*Please note you would need to replace 'TABLE NAME' with the name of your table where this data is stored. Also, Total AIF is the name of the calculated column.
This measure will sum the AIF for each carrier, regardless of time frame. SO if you wanted to compare the difference between Carriers, you could make a carrier specific formula to add up the AIF like so:
Qantas AIF = CALCULATE(SUM([TOTAL AIF]),'TABLE NAME'[CARRIER]="QF")
From here, you could create a formula for each carrier if you wanted to compare the differences between each. You should also be able to slice these formulas by the [date] column in your data visualizations.
Hi TheOckieMofoTheOckieMofo,
i'll give it a go and see what happens :)
However AIFs do not need to be summed up as it would be inappropriate: this will be a benchmark tool and we need to understand the position of every competitor for each period...
let me know if it's unclear
You can replace SUM in the formulas to MIN, MAX, AVERAGE, whatever aggregate function makes sense for the data.