Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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's avatar
Anonymous
Not applicable
8 years ago

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...

mow700's avatar
mow700
Resolver I
8 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Unfortunately I cannot use the expresssion below, because the RELATE function wants a column for argument, however 'Rate' is a measure and not a table, hence PBI won't accept it. 

     

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Microsoft Employee
      8 years ago

      Hi Anonymous,

       

      Can you please share your dummy .pbix file please? That would be great for us to debug. BTW, did you try this feature?

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      Best Regards!

      Dale

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi Dale,

         

         I've uploaded a dummy file here. My end goal is to have a gauge where if I select a data range, operator and type of failure the value will be the average of the rates for all the months. included in the data range I selected. At the moment the value shown by the gauge is simply the sum of the rates, and not the average. As I understand it, there are currently 2 issues:

         

        1- value is a total and not the average of the monthly rates

        2- even if the value was an average, since the Rate measure doesn't return any value for months where there were no failure events, the average would be wrong. I need Rate measure to return "0" every month when there are no failure events.

         

        Thank you!