Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Jruiz218
Frequent Visitor

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:

| 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:

 

Example.jpg

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

1 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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

10 REPLIES 10
sara11
Helper I
Helper I

@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.

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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
Super User
Super User

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.

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



 

@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,

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



@MFelix 

 

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

 

Thank you so much for taking a look at it!

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

@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)

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors