create a table from another table plus zeros

[Anonymous]

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!

[Greg_Deckler]

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.

[Anonymous]

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]

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]

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]

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]

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]

@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

[Anonymous]

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

[Anonymous]

@Vvelarde

[v-jiascu-msft]

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

[Anonymous]

@v-jiascu-msft

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!

[Anonymous]

@v-jiascu-msft

 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!

[v-jiascu-msft]

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

[Anonymous · Accepted solution]

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.

[v-jiascu-msft]

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