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.

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!

      • Vvelarde's avatar
        Vvelarde
        Community Champion
        8 years ago

        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