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

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

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)

Capture.PNG

 

 

 

 

 

 

 

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

 
1 ACCEPTED SOLUTION
v-gizhi-msft
Community Support
Community Support

Hi,

 

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

21.png22.png

23.png

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:

24.png

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:

25.png

Here is my test pbix file:

pbix 

 

Best Regards,

Giotto Zhi

View solution in original post

4 REPLIES 4
v-gizhi-msft
Community Support
Community Support

Hi,

 

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

21.png22.png

23.png

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:

24.png

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:

25.png

Here is my test pbix file:

pbix 

 

Best Regards,

Giotto Zhi

Anonymous
Not applicable

Hello

 

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

parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Hi, you're right I'm trying this

Thank you !

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.