Forum Discussion
Solved
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 ...
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])
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.