Forum Discussion
Excel Pivot Table - Power Pivot - Average Help
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])
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! 🙂
Glad I could help.
DISTINCT(SELECTCOLUMNS(----) creates a two column intermediate table without duplicate rows (ID and value). Like this:
ID, value
a, 31
b ,31
c, 120
And then the AVERAGEX function takes that intermediate table and averages the [value] column.
Hello Riny_van_Eekelen
I realized that after posting and was going to look at fixing my bad => Thanks for doing it
(no idea why but it seems I can't delete a post here, will revise it...)
