Forum Discussion
Excel Pivot Table - Power Pivot - Average Help
This is my sample table data, and my pivot table. I am trying to get the pivot table to show the average duration it took for the two applications to get approved, 75.5 days. I created a pivot table with data model, and put add on for power pivot. I tried some measures, but could not get any to work, I always get 53.25, any help would be appreciated, thank you.
Lorenzo But what if another ID also has a duration of 31 days? Then the average should become (31+31+120)/3 = 60,67
You'll need a measure that takes distinct ID's into account. Not just distinct duration values. Such a measure would look like this:
=AVERAGEX(DISTINCT(SELECTCOLUMNS('Table', "ID", 'Table'[ID], "value", 'Table'[value])), [value])
12 Replies
Lorenzo But what if another ID also has a duration of 31 days? Then the average should become (31+31+120)/3 = 60,67
You'll need a measure that takes distinct ID's into account. Not just distinct duration values. Such a measure would look like this:
=AVERAGEX(DISTINCT(SELECTCOLUMNS('Table', "ID", 'Table'[ID], "value", 'Table'[value])), [value])- Thank you so much, I got it to work. Now I need to study every part of it to understand it, I will do that this weekend! 🙂
- Rather bizarrely, I've just removed the Node ID field from the PivotTable Fields List and the Average function works perfectly! Even after refreshing or adding more data, it works.
- A rather clunky work around is to include the Node ID field beneath the Power Source ID field in your Pivot Table and then collapse it. You can then average the ApprovalDuration field.
- Hi. I can see where the issue is occurring. Your Pivot is averaging the two entries in the PivotTable rather than the four entries in the Sample Data. Unfortunately, I can't seem to get a work around. Perhaps you need to add all the ApprovalDuration to the PivotTable and group them before averaging?
