Forum Discussion
create a table from another table plus zeros
Dear all,
I'm struggling with the following and was hoping for some help. I have created a measure called "Rate" that looks like this if I use a matrix to visualize it:
| Date | Operator A | Operator B |
| Jan-15 | 0.2 | 0.36 |
| Mar-15 | 0.4 | 0.15 |
| Dec-15 | 0.3 | 0.42 |
I would like to create another measure that would consider all the months in the year and put "0" where there is missing data. The time frame to be filled (in this example the whole 2015 - see below) would have to come from a column located as a table called "MM YYYY"
| MM-YYYY |
| Jan-15 |
| Feb-15 |
| Mar-15 |
| Apr-15 |
| May-15 |
| Jun-15 |
| Jul-15 |
| Aug-15 |
| Sep-15 |
| Oct-15 |
| Nov-15 |
Dec-15 |
Measure I want to obtain
| Date | Operator A | Operator B |
| Jan-15 | 0.2 | 0.36 |
| Feb-15 | 0 | 0 |
| Mar-15 | 0.4 | 0.15 |
| Apr-15 | 0 | 0 |
| May-15 | 0 | 0 |
| Jun-15 | 0 | 0 |
| Jul-15 | 0 | 0 |
| Aug-15 | 0 | 0 |
| Sep-15 | 0 | 0 |
| Oct-15 | 0 | 0 |
| Nov-15 | 0 | 0 |
| Dec-15 | 0.3 | 0.42 |
Finally, I'd like to create a measure to calculate the average of those numbers. For example, if I select with a data slicer "Operator A" and with a time slicer "Jan 2015 to August 2015" I would like the measure to calculate:
New Measure = (0.2+0+0.4+0+0+0+0+0)/8 = 0.075
Thank you!
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.
15 Replies
Easiest way to accomplish the first task would be to create a column in your "MM YYYY" table with a formula of:
Column = 1
Place that in your matrix visualization as a Value or maybe Row and this should force the matrix to display your zero values.
Thanks.
I've tried adding the column to the MM-YYYY table and place it in the matrix visualization, however that didn't change the way the matrix is shown, i.e. only the months with data in it are displayed.
I've tried removing the relationship between the MM-YYYY and the dates in the Rate table, and the matrix did list all 2015 months, however the rate was showing for every month as the sum of all the rates, i.e. 1 fixed values for all months, including the ones that should have zeroes...
I think I would try to add a calculated column to the MM-YYYY table that pulled the related values from Rates table. You can set a default value of 0 for rows without related rates. Maybe something like:
Operator A= IF(ISBLANK(RELATED('Rates'[Operator A]) = TRUE, 0, RELATED('Rates'[Operator A]))Then create the measure against the MM-YYYY table.
