cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
abartozzi Member
Member

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

Accepted Solutions
abartozzi Member
Member

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

 

 

7 REPLIES 7
fhill Established Member
Established Member

Re: Differential for specific values

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

Re: Differential for specific values

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.

Highlighted
abartozzi Member
Member

Re: Differential for specific values

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

abartozzi Member
Member

Re: Differential for specific values

Hi @TheOckieMofoTheOckieMofo,

 

i'll give it a go and see what happens Smiley Happy

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

Re: Differential for specific values

You can replace SUM in the formulas to MIN, MAX, AVERAGE, whatever aggregate function makes sense for the data.

v-ljerr-msft Super Contributor
Super Contributor

Re: Differential for specific values

Hi @abartozzi,

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

abartozzi Member
Member

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

 

 

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 42 members 1,201 guests
Please welcome our newest community members: