Measures not returning the same values on chart as card.

[Farank]

Hi all,

I am trying to get the averages for date differences of when certain emails are sent.  The difference being from completion date/time to the trigger run date/time.  I have built in flags for the particular trigger run date/times in question.  They are flagged either 1 or 0 for OSR and OIF respectively.  

The measure to get OSR times is as follows:

Pumps Offline to Report Sent Latency (min) = DATEDIFF( MAX(trigger_metrics_dashboard[Completion Date/Time] ) ,

CALCULATE( MAX( trigger_metrics_dashboard[Trigger Run Date/Time] ) ,

FILTER( trigger_metrics_dashboard , trigger_metrics_dashboard[OSR] = 1 )

) , MINUTE

)

And the measure to average these are:

Average Report Sent Time (min) = CALCULATE( AVERAGEX(trigger_metrics_dashboard, [Pumps Offline to Report Sent Latency (min)]),

FILTER(trigger_metrics_dashboard, trigger_metrics_dashboard[Latency from ISIP] > 0)

)

When I display these on a card and on a visual for the week, the values do not match in both visuals as shown in the screen shot below:

 The card returns 39.02, the line chart returns 39.19, and excel (after exporting the data) returns 38.99 for Average Report Sent Time.

Oddly though, the chart and the card does return the same value for Average Inventory Upload Time (19.09).  The measures for those are as follows:

Pumps Offline to Inventory Upload Latency (min) = DATEDIFF( MAX( trigger_metrics_dashboard[Completion Date/Time] ) ,

CALCULATE( MAX( trigger_metrics_dashboard[Trigger Run Date/Time] ) ,

FILTER( trigger_metrics_dashboard , trigger_metrics_dashboard[OIF] = 1 )

) , MINUTE

)

AND

Average Inventory Upload Time (min) = CALCULATE( AVERAGEX(trigger_metrics_dashboard, [Pumps Offline to Inventory Upload Latency (min)]),

FILTER(trigger_metrics_dashboard,

trigger_metrics_dashboard[Latency from ISIP] > 0

)

Any help would be greatly appreciated.  I have no idea why/how it could return different values.

[v-yingjl · Accepted solution]

Hi @Farank ,

Based on your description, I tried to re-build the data sample but seems like could not reproduce it, the measure in the card shows the same value in the chart visual. You can check my sample file in the below.

All of the measures are the same as your posted measure, perhaps I could miss something that you can share more details about it or modify directly in my sample so that I could help you better. In addition, you can also sharing your sample file if it is convenient.

Best Regards,

Yingjie Li

[Farank]

I'm attaching my sample, please see link below.  I've tried Average vs Averagex and several different filters.  Everything seems to return different values even if I set the week filter on the chart to just week 42.

https://1drv.ms/u/s!ApFWRm5Wa1WqarOANFa1wtJJD3c?e=sNb0Mm

[v-yingjl]

Hi @Farank ,

It is caused by the filters.

I found that you have set the week filter in the line chart in 'Filters on this visual'

It will only filter the values on the line chart visual, but actually the card visuals show the value based on week 42 and 41 in this case.

To solve this issue, you can just click the data point in the line chart to use the visual level filter to show the same result or put the week field in 'Filters on all pages'.

Please check the attached sample file in the below, hopes to help you.

In addition, you can refer this document about filter types:

https://docs.microsoft.com/en-us/power-bi/create-reports/power-bi-report-add-filter

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Farank]

Hello again @v-yingjl .

So, if I'm understanding this correctly, the cards are showing the values for two weeks and not just the last calender week as determined by the slicer?  The line chart shows both values for those two weeks.  Clicking on the week in question drills the cards down to that week?  I assume the cards values for those two weeks are not the same as averaging the values from the two weeks on the chart.

So (44.06 + 33.72 ) / 2 = 38.89 and not 34.06 like the card says.  I assume this is because it averages the two weeks as a whole on the card.

If I use the table on the second report page and export all the data for the last calender week where OSR = 1, the average in excel is 33.24.  All things appear to be equal and the excel value doesn't match the card like I would expect.

I'm just trying to understand the difference.  I would think the card would match the excel value.

I thank you again for any clarity you can provide.

[v-yingjl]

Hi @Farank ,

First I'm sorry about that I forgot to attach the file in the below yesterday. Please check the file under this post and refer it with my previous post.

"So (44.06 + 33.72 ) / 2 = 38.89 and not 34.06 like the card says. "

The average could not just add the two weeks value and divide 2.

About the average in excel that shows different values, in your picture you have selected the ISIP column to calculate the average directly but in power bi, you have used averagex() function and quotes [Pump] measure, it will affect the accuracy of the decimal point.

Since the average measure filter [Pumps] measure > 0, when I put the [Pumps] measure in page2 and set its value > 0 in the table visual filter, after I export data from this table and cacluate the average in excel based on the [Pumps] field, it will get the same result as the average measure in power bi:

Hopes it could help you better to understand.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Farank]

@v-yingjl   Haha.  You are correct.  Sometimes you just need another set of eyes.  Thanks for your help.