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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

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
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.