Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Differential for specific values

Hi Power BI team, 

 

As usual I need your help to get this sorted. 

 

I have this table where the following fields are listed:

 

headers.PNG

 

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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. 

 

 

View solution in original post

7 REPLIES 7
v-ljerr-msft
Employee
Employee

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. Smiley Happy

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]

r1.PNG

Regards

Anonymous
Not applicable

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. 

 

 

TheOckieMofo
Resolver II
Resolver II

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.

Anonymous
Not applicable

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.

fhill
Resident Rockstar
Resident Rockstar

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




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




Anonymous
Not applicable

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...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.