Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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:
Best Regards,
Giotto Zhi
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:
Best Regards,
Giotto Zhi
Hello
I really appreciate your help, thank you so much, I learnt some new functions !
@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.
Hi, you're right I'm trying this
Thank you !
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |