Forum Discussion
create a table from another table plus zeros
Hi! What I ended up doing is a creatig a calculated column in the Event Data table that looks like this:
Event_Month = TOTALMTD(COUNT('Event Data'[Event]),'Event Data'[Event Date Time].[Date])
This counts how many events per month happened.
Then I created a calculated column in the Utilization Data table to calculate the rate, which is the total event per month divided by the total hours for that month, for each operator and each car "model". I had to use an IF statement so that every time there were no events for a particular month, the result would be zero instead of a blank value. That allows me to calculate and visualize the average rate with a gauge visual and a time slicer for how many months I want.
Event Rate = IF(DIVIDE(CALCULATE(MAX('Event Data'[Event_Month]),FILTER('Event Data',IF('Event Data'[OPERATOR]= 'Utilization Data'[Operator],1,0)), FILTER('Event Data', IF('Event Data'[Model] = 'Utilization Data'[Model], 1, 0)),FILTER('Event Data', IF('Event Data'[Year-Mon]='Utilization Data'[Year-Mon2], 1, 0))),'Utilization Data'[Total Monthly Hours],0)>0, DIVIDE(CALCULATE(MAX('Event Data'[Event_Month]),FILTER('Event Data',IF('Event Data'[OPERATOR]= 'Utilization Data'[Operator],1,0)), FILTER('Event Data', IF('Event Data'[Model] = 'Utilization Data'[Model], 1, 0)),FILTER('Event Data', IF('Event Data'[Year-Mon]='Utilization Data'[Year-Mon2], 1, 0))),'Utilization Data'[Total Monthly Hours],0),0)
Thank you all for the help.
Anonymous
Hi, incorporate in your formulas the count of Months Selected.
MonthsSelected=Countrows(DateTable)
With this you can sum your Rates and Divide using MonthsSelected.
Like:
You have only 4 months with rates but you are selected 12 months,
1+3+5+8 / 12
I hope this help you
Regards
Victor
Lima - Peru
Thanks Victor,
I've tried that, it improved however it's still not correct. Let me explain to you.
- 'MonthsSelected' counts every time there is an entry for a particular operator.
- Say Operator A has one pneumatic event in January, and one mechanical event in February, nothing else for the rest of the year.
- If I want to calculate the ratio average of mechanical failures from January to August, before I implemented your function PBI was only picking up February.
- With your function it now picks up January and February because there were entries in those 2 months, however it doesn't pick up March to August because there are no entries. So the count of months improved from 1 to 2, but it needs to be 8!
Hope that's clear. Thanks a lot!
Michele
Hi! What I ended up doing is a creatig a calculated column in the Event Data table that looks like this:
Event_Month = TOTALMTD(COUNT('Event Data'[Event]),'Event Data'[Event Date Time].[Date])
This counts how many events per month happened.
Then I created a calculated column in the Utilization Data table to calculate the rate, which is the total event per month divided by the total hours for that month, for each operator and each car "model". I had to use an IF statement so that every time there were no events for a particular month, the result would be zero instead of a blank value. That allows me to calculate and visualize the average rate with a gauge visual and a time slicer for how many months I want.
Event Rate = IF(DIVIDE(CALCULATE(MAX('Event Data'[Event_Month]),FILTER('Event Data',IF('Event Data'[OPERATOR]= 'Utilization Data'[Operator],1,0)), FILTER('Event Data', IF('Event Data'[Model] = 'Utilization Data'[Model], 1, 0)),FILTER('Event Data', IF('Event Data'[Year-Mon]='Utilization Data'[Year-Mon2], 1, 0))),'Utilization Data'[Total Monthly Hours],0)>0, DIVIDE(CALCULATE(MAX('Event Data'[Event_Month]),FILTER('Event Data',IF('Event Data'[OPERATOR]= 'Utilization Data'[Operator],1,0)), FILTER('Event Data', IF('Event Data'[Model] = 'Utilization Data'[Model], 1, 0)),FILTER('Event Data', IF('Event Data'[Year-Mon]='Utilization Data'[Year-Mon2], 1, 0))),'Utilization Data'[Total Monthly Hours],0),0)
Thank you all for the help.
Hi Anonymous,
I noticed that the table "Event Data" only supplies a Failuretype. How about adding the Failuretype to the table "Utilization Data".
1. Add Failuretype to table Utilization Data.
FailureType = LOOKUPVALUE ( 'Event Data'[Failure Type], 'Event Data'[Year-Mon], [Year-Mon2], 'Event Data'[Operator], [Operator], 'Event Data'[Model], [Model] )2. I updated the measure Rate.
Rate 3 = VAR numerator = COUNT ( 'Utilization Data'[FailureType] ) RETURN 1000 * DIVIDE ( IF ( ISBLANK ( numerator ), 0, numerator ), SUM ( 'Utilization Data'[SumOfHours] ), 0 )3. Create a measure to evaluate average value.
Average Rate = AVERAGEX ( SUMMARIZE ( 'Utilization Data', 'Date'[Year-Mon], Operators[Operator], "R", [Rate 3] ), [R] )4. You have many dimension table. Such as Date, Operators. The columns of the visuals should come from these dimension tables.
You can check it out in this file: https://drive.google.com/open?id=0Bw_2xCilYNOSQzJQcXdPMGxqVjA.
Best Regards!
Dale
Thanks a lot Dale. I have one question about this method:
- What if in one single month an operator has more than 1 event, for example 1 electrical event and 2 hydraulic events? Would the "Failure Type" column only handle 1 event?
- What about applying your method a bit differently:
- Add 3 columns to the Utilization Data:
- Column called "Electrical Failure" which would count how many electrical failures for each operator for each month
- Column called "Mechanical Failure" which would count how many electrical failures for each operator for each month
- Column called "Hydraulic Failure" which would count how many electrical failures for each operator for each month
- Then create 3 different measures to calculate rates simply using data in the Utilization Data table
- Then create 3 different measures to average of the rates
I'm afraid I wouldn't know how to create the 3 columns with lookup and count functions together. Would you be able to assist with these?
Thanks!
Hi! I've tried to create the three columns to count the type of events for each operator, each month. However I am not able to create more than 1 column as PBI will give me an error.
A circular dependency was detected: Utilization Data[Electrical], Utilization Data[Column], Utilization Data[Eelctrical].
It looks like I cannot create more than one calculated column in the Utilization Data table. Hence I'll have to review the approach again, i.e. utilize measures instead of calculated columns.
Thanks!
Hi Anonymous,
You can add three columns this way:
Electrical Failure = VAR fp = LOOKUPVALUE ( 'Event Data'[Failure Type], 'Event Data'[Year-Mon], [Year-Mon2], 'Event Data'[Operator], [Operator], 'Event Data'[Model], [Model] ) RETURN IF ( fp = "Electrical", fp, BLANK () )Mechanical Failure = VAR fp = LOOKUPVALUE ( 'Event Data'[Failure Type], 'Event Data'[Year-Mon], [Year-Mon2], 'Event Data'[Operator], [Operator], 'Event Data'[Model], [Model] ) RETURN IF ( fp = "Mechanical", fp, BLANK () )Hydraulic Failure = VAR fp = LOOKUPVALUE ( 'Event Data'[Failure Type], 'Event Data'[Year-Mon], [Year-Mon2], 'Event Data'[Operator], [Operator], 'Event Data'[Model], [Model] ) RETURN IF ( fp = "Hydraulic", fp, BLANK () )Best Regards!
Dale
Hi Anonymous,
Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.
Best Regards!
Dale
