Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Power BI team,
As usual I need your help to get this sorted.
I have this table where the following fields are listed:
Basically each Fare Basis value has a specific time lenght (e.g. 24/10/2017 - 06/12/2017) and it features a different CXR (e.g. QF, SQ), a variable class (e.g. Y,J,F) a specific level, tfc. and AIF. Such periods do overlap but they will be charachterized by different CXR and therefore level, tfc and aif.
What i need to do is creating a measure that indidicates the AIF difference between QF and others CXR for specific periods (that therefore can be selected in a time range) and class.
Do you believe is there a way to make it?
thank you for your help
Alberto
Solved! Go to Solution.
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.
Hi @Anonymous,
What i need to do is creating a measure that indidicates the AIF difference between QF and others CXR for specific periods (that therefore can be selected in a time range) and class.
If I understand you correctly, the formulas below should work in your scenario.
AIF of QF = CALCULATE(SUM('EXPANDED PERIODS'[AIF]),'EXPANDED PERIODS'[CXR]="QF")
AIF of Other CXRs = CALCULATE(SUM('EXPANDED PERIODS'[AIF]),'EXPANDED PERIODS'[CXR]<>"QF")
AIF difference between QF and others CXR = [AIF of Other CXRs] - [AIF of QF]
Regards
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.
Please post some additional data for us to sample. Your picture only shows 'QF' CXR records, how do you know which record starts / stops a pair of recrods?
FOrrest
Proud to give back to the community!
Thank You!
Hi @fhillfhill,
i've attached a google drive link where you can find the dataset.
https://drive.google.com/open?id=1MHGgn4EGQJ-eIoQS0U2Isq5y-bYxsV21ghB_OBJmXUM
Also i think i have to add week number because the next step would be adding the difference between this week and last week...
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |