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])
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 added to the measure to also include Request Power column incase two power applications are submitted 1 year apart with the same power source ID, so each average is counted, and it works good in the test file.
I did have one more question, I tried to get the measure to work in my live data tracker, and the measure returned some type of error about strings, like data cannot be a string. Is there anyway to add some type of code so it ignores these strings/invalid data when computing the intermediate table so it does not error? Thanks
=AVERAGEX(DISTINCT(SELECTCOLUMNS('MasterTracker', "Power Source ID", 'MasterTracker'[Power Source ID], "Request Power", 'MasterTracker'[Request Power], "ApprovalDuration", 'MasterTracker'[ApprovalDuration])), [Average of ApprovalDuration])Riny_van_EekelenSpecifically this is the problem I am having, if the duration formula does not find a number in Power Design Received, it returns a string "", and this makes AverageX break.
- Thank you for explaining.
