Forum Discussion
Grouped Average
I have a little rule that says if you have the option of doing it in TSQL before you get to Power BI then thats usually better since the model will be faster but assuming you cant do that:
so you've mentioned a COUNT a SUM and an AVERAGE but It looks like you just use Average - is that like a daily average? so in the first column you had on average 31.3 items overdue each day?
You could just build one measure the calculates "Daily Average"
Something like
Overdue Items = COUNTROW(Table)
Days = DISTINCTCOUNT(Table[Date]) -- note but be a date not a datetime
Daily Average = DIVIDE([Count of Overdue],[Days],0)
Then you put that measure in Values, Week_End in the Axis, Group in the Legend.
for each Week and Group it will determine how many items divided by how many distinct days??
is that what you are after?
Hi Noob :)
You can use Power Query "group By" to do this is a couple of clicks:
open the query editor for the data and and click "Group By" under Transform
Remember to add all the columns you want to see in the "Group by" section, and the default Count column will do what you want - i.e. Count the number of rows that are the same for the "group by" columns.
click OK and Viola!
I think this is right... let me know.
Cheers
Greg
Thanks this trick helped a lot; I made my grouped column then merged it back to my main table. Maybe not the best way but now I can make morelculations with it :)