Create a column in a table include the filters of the report part

[Anonymous]

Hello guys,

Actually I have 2 tables CAR_MODEL and INCIDENT in my Data Model with the relation 1..*
In fact, the tab INCIDENT allows gather all incidents happened to each car model. An incident has an OPEN DATE and CLOSE DATE (date when it is resolved).

In the tab CAR_MODEL, I had created 2 column to calculate for each car model :

-the time to treat all incidents: Sum of datediff between OPEN DATE and CLOSE DATE (Treatment time SUM)

-the average of the time above (Treatment time AVG)

Now,  I would like to add another column calculate the gap between the Treatment time AVG and the average of itself. 

The problem is that doesn't include all filters that I'm using in the report part so the result of the Average of the Treatment time AVG is not correct.

I had created a measure to calcul the Average of the Treatment time AVG. Then, to get the gap, I tried to substract the Treatment time AVG by this measure above but Power BI doesn't allow this way.

Could you help me please ? 

Thank you

[v-gizhi-msft · Accepted solution]

Hi,

According to your description, I create two tables to test:

Please take following steps:

1)Create two columns to calculate ‘Treating time SUM’ and ‘Treating time SUM’:

Treating time SUM

= VALUE(FIXED(DATEDIFF(Incident[Open Date],Incident[Close Date],SECOND)/3600,2))

Treating time AVG

= CALCULATE(AVERAGE(Incident[Treating time SUM]),ALLEXCEPT(Incident,Incident[Incident]))

And it shows:

2)Create a column to calculate the average itself:

Average = AVERAGE(Incident[Treating time SUM])

3)Create a column to calculate the gap:

GapTime = Incident[Treating time AVG]-Incident[Average]

4)The result shows:

Here is my test pbix file:

pbix 

Best Regards,

Giotto Zhi

[Anonymous]

Hello

I really appreciate your help, thank you so much, I learnt some new functions !

[parry2k]

@Anonymous not sure why you are adding these as calculate columns instead add these measure, and it would work until I'm missing something.

[Anonymous]

Hi, you're right I'm trying this

Thank you !