Please Help!!! Grand Total Calculating Wrong Average - Power Pivot

[Jruiz218]

Hello!

Im migrating a report from regular Excel tables/Pivots over to PowerPivot, currently this is my scenario:

In the old version of the report my source data came in the following format:

| 10001 | Created Date/Time | Resolved Date/Time | Description |

| 10002 | Created Date/Time | Resolved Date/Time | Description |
| 10003 | Created Date/Time | Resolved Date/Time | Description |
| 10004 | Created Date/Time | Resolved Date/Time | Description |

| 10005 | Created Date/Time | Resolved Date/Time | Description |

So on for a few thousand rows...

based on that format, I had a few calculations, including one to identify the amount of time the record was open ( Resolved Date/Time - Created Date/Time), using a simple formula (-), so my data was:

| 10001 | Created Date/Time | Resolved Date/Time | Description | Time Open |

| 10002 | Created Date/Time | Resolved Date/Time | Description | Time Open |

| 10003 | Created Date/Time | Resolved Date/Time | Description | Time Open |

| 10004 | Created Date/Time | Resolved Date/Time | Description | Time Open |

| 10005 | Created Date/Time | Resolved Date/Time | Description | Time Open |

I used this to create a pivot table that would give me the Average Calculation of the Time Open column by date (sum of Time Open / count of records)

In when I created the DataModel and moved it to power pivot, I splitted the records into several records using the description field, now the data looks like this:

| 10001 | Created Date/Time | Resolved Date/Time | Description A |
| 10001 | Created Date/Time | Resolved Date/Time | Description B |
| 10001 | Created Date/Time | Resolved Date/Time | Description C |
| 10001 | Created Date/Time | Resolved Date/Time | Description D |
| 10002 | Created Date/Time | Resolved Date/Time | Description A |
| 10003 | Created Date/Time | Resolved Date/Time | Description A |
| 10003 | Created Date/Time | Resolved Date/Time | Description B |
| 10004 | Created Date/Time | Resolved Date/Time | Description A |
| 10004 | Created Date/Time | Resolved Date/Time | Description B |
| 10005 | Created Date/Time | Resolved Date/Time | Description A |
| 10005 | Created Date/Time | Resolved Date/Time | Description B |

| 10005 | Created Date/Time | Resolved Date/Time | Description C |

Then I used the same calculation (Resolved Date/Time - Created Date/Time) To create the Time Open Column, however when i try to create a pivot table using the data, the Grant Total Calculation is showing wrong Values:

Excel Seems to be doing an unique count of the records based on the ID, however the grand total appears to be doing a different calculation.

Thank you very much in advance

[sara11]

@MFelix 

Hi, I don't know if you could help me with a question that is similar to this one. I want to calculate the average of the difference between two columns (max and min) for the different attempts that I have and that range from 1 to 5, where both the max column and the min column have to be greater than 0. When they aren't, I want it to be blank so it doesn't enter in the average calculation. I've tried different measures, but without success. The average total is always different. Can you help me, please?
I have a dummy table available to help.
Thank you very much in advance.

[MFelix]

Hi @sara11 ,

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[sara11]

MFelix,

Thank you very much for your willingness to help me. I sent you, by private message, the link to download my data, as well as the measures I wrote.

[MFelix]

Hi @Jruiz218 ,

What is happening is that measures are calculated based on context so what you are picking up on the total is based on the Created - Resolved of the full information of your data.

You need to had an AVERAGEX formula for your total something similar to this:

IF(HASONEVALUE(Calendar[WEEKNUMBER]), Table[Resolved] - Table[Created],AVERAGEX(Calendar,Table[Resolved] - Table[Created]) )

I'm assuming you have a calendar table for the dates.

[Jruiz218]

@MFelix  Thank you so much for taking a look at this!

I tried creating a new column and using your calculation, but the resul on the Grand Total its exactly the same as for my original calculation.

However, now you mention context I noticed the Resolved column might present 3 diff scenarios which might be causing this:

A) Resolved value is after Created value (i.e. Resolved: /1/1/2020 13:05:00 / Created: 1/1/2020 12:00:00) the return for this would be a positive value (1:05:00)

B) Resolved value is before Created value  (i.e. Resolved: /1/1/2020 12:00:00 / Created: 1/1/2020 13:05:00) the return for this would be a negative value (this is due a bug on the tool that generates the raw data)

C) Resolved value is Blank (item still open at the time to generate the raw data)

to correct this I had the following adjustment on my original formula 

=IF([Resolved]-[Created]<0,0,[Resolved]-[Created])

this would change any negatie value/blank value to 0, I wonder if would be possible to adjust yours with something simillar to sort out that inconvenience.

Regards,

[MFelix]

Hi @Jruiz218 ,

Try the following calculation below.

var Time_Opened = IF(Table[Resolved] - Table[Created] <0 , 0 , Table[Resolved] - Table[Created])
Return
IF(HASONEVALUE(Calendar[WEEKNUMBER]), Time_Opened,AVERAGEX(Calendar,Time_Opened) )

 If this does not work can you share a mockup file please.

[Jruiz218]

@MFelix 

I just sent you the link to the report via private message.

Thank you so much for taking a look at it!

[MFelix · Accepted solution]

Hi @Jruiz218 ,

Based on the data you have send out you need to do the following:

• Create a measure with the Average of the Hours Handled (this will be used as an intermediat step)

Measure_Average_Hour_Handle =AVERAGE('Dummy Data'[Hours Handled])

• Add another measure:

Average_Hours_Handled =
IF (
    HASONEVALUE ( 'Calendar Resolved'[Date] );
    [Measure_Average_Hour_Handle];
    AVERAGEX (
        SUMMARIZE (
            'Calendar Resolved';
            'Calendar Resolved'[Date];
            'Calendar Resolved'[Week Number]
        );
        [Measure_Average_Hour_Handle]
    )
)

Should work as expected.

[Jruiz218]

@MFelix Amazin! it worked right away! thank you so much!

[Jruiz218]

@MFelix  And yes, im using a calendar table that is setup as follows 

One to Many connections 

Calendar Resolved [Date]    to      Table[Resolved Date]

Resolved Date is a Column in the raw data wich one contains date (no time) (13/12/2020)