cancel
Showing results for
Did you mean:
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:

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?

Alberto

1 ACCEPTED SOLUTION

Accepted Solutions
Member

## Re: Differential for specific values

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

Member

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

Member

## Re: Differential for specific values

Hi @fhillfhill,

i've attached a google drive link where you can find the dataset.

Also i think i have to add week number because the next step would be adding the difference between this week and last week...

Highlighted
Member

## Re: Differential for specific values

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

Member

## Re: Differential for specific values

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

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.

`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

Member

## Re: Differential for specific values

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.

Announcements

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

#### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 74 members 1,138 guests
Recent signins: