Forum Discussion
SUM values only for distinct values in another column
Thanks everyone for your helpful suggestions. In the end, I did some work in my SP to add a new column for my average calc like this:
Unique ID Version Ave Calc 1234 1 1 1234 1 NULL 1234 1 NULL 1234 1 NULL 1234 2 2 1234 2 NULL 1234 3 3 1111 2 2 1111 2 NULL 2223 3 3 3556 2 2 3556 2 NULL 3556 2 NULL 4432 1 1 The NULLS are needed rather than zeros otherwise the average is affected by the extra rows.
It would be amazing if in future versions of PowerBI it was possible to perform calculations on values used in the visualisations - although I appreciate this will have possible performance implications if missused.
Well yes - this is true! I guess I was wondering how I could get what I need without having to go back to the SP....
Any pointers on acheiving the same thing in SQL would be welcome!
If I were doing this in DAX, I would write a measure like this:
Average Version = DIVIDE(SUMX(VALUES(Table1[Version]),[Version]),COUNTROWS(Table1))
Thanks everyone for your helpful suggestions. In the end, I did some work in my SP to add a new column for my average calc like this:
Unique ID Version Ave Calc 1234 1 1 1234 1 NULL 1234 1 NULL 1234 1 NULL 1234 2 2 1234 2 NULL 1234 3 3 1111 2 2 1111 2 NULL 2223 3 3 3556 2 2 3556 2 NULL 3556 2 NULL 4432 1 1 The NULLS are needed rather than zeros otherwise the average is affected by the extra rows.
It would be amazing if in future versions of PowerBI it was possible to perform calculations on values used in the visualisations - although I appreciate this will have possible performance implications if missused.