Forum Discussion
Having a problem calculating average after doing a pivot on columns
Hi,
I have a Table with unique IDs, their group number, avg days they need to perform a task and the category each ID is part of. Sometimes an ID is part of 1 category sometime 2 or 3 and sometime 0. I needed to unpivot in order to have all the categories name into one column.
Except now when I calculate the average of days for a specific group it is different than before i unpivoted the data because the id with multiple group have are now counted multiple times. I tried doing a measure :
AVG DAYS measure = CALCULATE(AVERAGE('Sheet1 with pivot'[AVG days]),DISTINCT('Sheet1 with pivot'[AVG days]))
but the result is the same.
What could i do to solve my problem ?
PS : Here is the sample data (EXCEL + PBIX FILE)
https://drive.google.com/open?id=1PHdNlVqLT3i4Isd-HK7QxLDIH0JVqa4i
I found a way to solve my problem by putting a "is null" string in all the null cell in my table that way when i pivot the columns all the IDs are counted the same number of time thus putting the average of days back to normal. Its not very pretty because it creates many more rows but it works !
If anyone has a cleaner solutions share it :p
Nicolas
1 Reply
I found a way to solve my problem by putting a "is null" string in all the null cell in my table that way when i pivot the columns all the IDs are counted the same number of time thus putting the average of days back to normal. Its not very pretty because it creates many more rows but it works !
If anyone has a cleaner solutions share it :p
Nicolas