Forum Discussion

Jruiz218

Frequent Visitor

6 years ago

Solved

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

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:

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:

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)

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

Need Help

MFelix

6 years ago

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.

10 Replies

MFelix
Super User
6 years ago
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
  Frequent Visitor
  6 years ago
  
  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
    Super User
    6 years ago
    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.
sara11
Helper I
3 years ago
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
  Super User
  3 years ago
  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
    Helper I
    3 years ago
    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.

Forum Discussion

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

10 Replies

Related content

moving average help

Calculation Help

Calculation Groups help

help calculation

Calculation Help