Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have been stuck on this for a couple of days now.
The scenario is like this, I have a table that is a "weekly performance" list for a population of machines and their performance with each machine having a unique ID. So the Table is Machine_ID, Week_Date, Performance.
I have also added an Index key that is the concatenation of Machine_ID & Week_Date
The second table "serviced machines" lists what machines were serviced in a particular week. So the Table is Machine_ID, Week_Date and a concatenation to create an Index as above.
What I want to do is take all the Machine_ID's that were serviced in a week and calculate their average performance for that week AND ALSO what was there average performance 2 weeks earlier.
I have tried a number of ways but with without success -- any ideas how I could do this?
Need sample/example data in text form. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
That being said...See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...
Hi Greg,
Thanks for the link very useful and hopefully I have managed to take most on board with this reply. I looked at your MTBF article it was interesting and I think maybe someway to what I want to do but i am specifically trying to use subgroups of machines and seeing the before and after some Activity was applied.
Problem Statement
I'll use a a simplified model to explain as the implementation is with a much larger model we run.
Assume there is a population of 100 machines/objects and every week as well as the normal data we get on these we also get Field data on UpTime for a subset of the base. We perform work on a small percentage of the machines every week and the "Exam Question" I want to answer is for the small subset of machines were work was perform, and we have UpTime data, did their Average UpTime improve when we compare their average UpTime 2 weeks before the action and 2 weeks after and calculate this each week for the weeks unique small pool of machines.
I have the following Tables in the Model
Calendar |
Used to group dates into weeks |
History |
Holds by week data relevant to how the machine was performing along with other data used to slice the model |
Activity |
Each week a small percentage of the machines have work done to them this table documents that activity |
FieldData |
This table holds by week a subset of the machines in the History Table where we get Uptime performance by machine. |
The relationship currently flow top down in the order of the list above
Relationships | ||
Calendar | History | |
WeeskStart | 1:* | WeekStart |
History | Activity | |
WeekStart_ID | 1:* | WeekStart_ID |
Activity | FieldData | |
WeekStart_ID | 1:* | WeekStart_ID |
Answering the Exam Question
I have simplified the calculations I am looking for by looking at 1 week in this explanation but I need the solution to iterate through all the weeks.
In the week of 22/04/2018 there where 5 machines that had work done to them so if we filter this weeks ACTIVITY table for 22/01/2018 and is there a record in FieldData table we see -
Table - ACTIVITY | ||||
WEEK | ID | WeekStart_ID | Other field used in slicers | |
22/04/2018 | ID123 | 22/04/2018_ID123 | … | |
22/04/2018 | ID976 | 22/04/2018_ID976 | … | |
22/04/2018 | ID532 | 22/04/2018_ID532 | … | |
22/04/2018 | ID232 | 22/04/2018_ID232 | … | |
22/04/2018 | ID345 | 22/04/2018_ID345 | … |
and in the FieldData table (unfiltered just now) we would have many records -
Table - FeildData | |||
WEEK | ID | WeekStart_ID | Uptime |
… | |||
22/04/2018 | ID123 | 22/04/2018_ID123 | 0.98125 |
… | |||
01/07/2018 | ID999 | 01/07/2018_ID99 | 0.99658 |
From this data I am trying to do the following
The calculation: | Take the 5 ID's in week 22/04/2018 and create a table from FieldData that contains all the records associated with these 5 ID in the table | ||||
Then | |||||
Average Uptime (-2 Weeks) = CALCULATE(AVERAGE(FieldData[Uptime], DATEADD([CALENDAR[WEEK}, - 14, DAYS) | |||||
Average Uptime (+2 Weeks) = CALCULATE(AVERAGE(FieldData[Uptime], DATEADD([CALENDAR[WEEK}, + 14, DAYS) | |||||
Diff in Uptime = Average Uptime(+2 Weeks) - Average Uptime(-2 Weeks) | |||||
Expected results
A table that covers the period we have data on
Week | Average Uptime (-2 weeks) | Average Uptime (+2 weeks) | Diff in Uptime |
01/04/2018 | 99.844% | 99.945% | 0.101% |
08/04/2018 | 98.424% | 99.546% | 1.122% |
15/04/2018 | 96.425% | 98.675% | 2.250% |
22/04/2018 | 98.354% | 98.987% | 0.633% |
29/04/2018 | 98.999% | 99.125% | 0.126% |
06/05/2018 | 97.568% | 98.832% | 1.264% |
Current Status
I tried a number of approaches and still stuck - it might be I am looking at this from the wrong prospective I know its possible. In writing up this I am question myself in do I have the structure correct maybe my FeildData needs to have a relationship with the History table and not the Activity Table
Any help/guidance would be gratefully appreciated.
Cheers
Hi @Timaru_Golf,
How do you calculate the [Uptime]? In your sample table, how to get 0.98125 and 0.99658?
Best Regards,
Angelia
Hi,
In the FieldData table each machine has a row per week where the uptime for that week is stored.
Regards
Andrew
Today I have been trying to break the problem into mini steps and build up to a solution..
So first how many units where touch in a week. The following measure works used in a Matrix table Week for the Rows works fine
Units Touched = COUNTROWS(VALUES(FieldData[ID])
Next I tried to create the same results but now opening the data up to the full table so I could walk back and forth in the data later but just now keep it this week.
TEST ALL Filtered this week & ID's = VAR Units_in_this_week = VALUES(FieldData[ID]) RETURN CALCULATE( COUNT(FieldData[ID]), FILTER( ALL(FieldData), COUNTROWS( FILTER( FieldData, EARLIER(FieldData[Date]) = DATEADD(Calendar[WeekStart], -0, DAY) && FieldData[ID] in Units_in_this_week ) ) ) )
The Above Formula I adapded from a post by Alejandro Zuleta and link is
From what I can see it is basically ignorning the "Units in Field" condition and just returning all the records that week.
So what am I doing wrong with -- && FieldData[ID] in Units_in_this_week -- or just my basic logic?
Regards
Andrew
Hi @Timaru_Golf,
You need to list the specific value for Units_in_this_week part, please review the IN function.
Thanks,
Angelia
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |