calculate a measure with 2 contexts

[Anonymous]

Hello
I have 2 tables CARS_MODEL and INCIDENT with the relation 1..*
A car model has a name and belong to a group so a group could own n car model.
An incident has OPEN DATE and CLOSE DATE. I added in the tab INCIDENT a column to calculate the gap beetween these 2 dates (named Treating Time).Then, I created a measure calculating the average of TREATING TIME (named Treating Time Average) that I used as value in my bar chart and with attribute GROUP as Axe. So I have the average of time that each group needs to treat an incident. So each group has a different average.

Now I would like to calculate the gap between this Average and the average of time that 1 group needs to treat an incident(a measure that I created named Average Group). In fact. I programed a measure to substract Treating Time Average to the Average Group in order of getting the gap. Nevertheless, when I put this measure to the value of the bar chart, I don't have a correct result and I think because of the context. It includes only the current group.

Could you help me to find a solution please ?

Thank you

[Anonymous]

@JarroVGIT 

@TomMartens 

Hello, thank you for your answers. You can finds the PIBX in this link. 

https://drive.google.com/open?id=1rxmoe4dBLWWFDfpwDPy6QzvNzAivxNo9

I added some inconsistent data to make the PIBX robust in case the real data set contains some errors.

ex: A Car Model has no incident and vice versa or an incident doesn't have a close date.  We ignore data of these cases

To respect this condition , I created Incident? in the tab Car Model and       Car Model? in the tab Incident to identify the data which are in full join( without intersection). the value = False

With the solution with the function allexcept() , I think that I got the correct result but I'm not sure.

Could you tell me how can I do to avoid to create 2 these columns Incident? and Car Model? 

[Anonymous · Accepted solution]

@Anonymous 

Following measures should work without the 2 columns: 

avg = CALCULATE(AVERAGE(Incident[Treating Time]),FILTER(Incident,Incident[Car Model] =RELATED(Car[Car Model])))

avg group = CALCULATE([avg] / CALCULATE(DISTINCTCOUNT(Car[Group]),FILTER(Car,[Car Model] in VALUES(Incident[Car Model]))),ALLEXCEPT(Incident,Car[Owner]))

gap = CALCULATE([avg]-[avg group])

Pbix if needed : https://qiuyunus-my.sharepoint.com/:u:/g/personal/paul_qiuyunus_onmicrosoft_com/ESiapJK9GZFIrducocz-TNIBEVyKZ4Y2lXTE2vJl5SWFgw?e=9DwtOJ 

Paul Zheng
Don't forget to hit THUMBS UP and mark it as a solution if it helps you!

[Anonymous]

I opened your PIBX and I noticed that all groups have an average of treating time >10 but the average goup is  ~4 . It is not possible so I think there is something wrong in this measure

[Anonymous]

I would like to asking you a details concerning the DAX function ALLEXCEPT : 

You following formula works actually with ALLEXCEPT(Incident;Car[Owner]))

avg group = CALCULATE([avg] / CALCULATE(DISTINCTCOUNT(Car[Group]);FILTER(Car;[Car Model] in VALUES(Incident[Car Model])));ALLEXCEPT(Incident;Car[Owner]))

I tried to change from ALLEXCEPT(Incident;Car[Owner])) to ALLEXCEPT(Incident;Car[Car Model])) and it worked

but if I change to ALLEXCEPT(Incident;Car[Group])) , I won't obtain the correct result

I will add some others attributes to the tab CAR which will be used as filter to the report part. So which attribute I have to used in the function ALLEXCEPT to get the correct result with all the futur filters

Thank you  and Happy new year 🙂 ! 

[JarroVGIT]

Hi @Anonymous ,

Like @TomMartens mentioned, some sample data would help because then we would be able to reproduce and try out different solutions. However, based on your description I think you need to create the measures like this;

Treating Time Average all Groups = 
CALCULATE(AVERAGE(INCIDENT[TREATING TIME], ALL(INCIDENT))

Treating Time Average Current Group = 
AVERAGE(INCIDENT[TREATING TIME])

Diff Per Group =
[Treating Time Average all Groups] - [Treating Time Average Current Group]

The first one will always return the same value, because it undoes all filters on INCIDENT. The second one does take into account the current context (in your case, the GROUP column as it is the filter basis of your bar chart). The second one substracts the second measure from the first measure. 

Let me know if this works!

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[TomMartens]

Hey @Anonymous ,

I have to admit that I have my difficulties to follow your description.

Please provide the DAX statements for the measures you created, even better, create a pbix that contains sample data but still represents your data model, upload the pbix to onedrive or dropbox and share the link.

Regards,

Tom