Forum Discussion
Grouped Average
Hey,
I need to get the sum amount values (per Group and per Month and per Week_Timestamp)
I am just not able to get the values as "grouped". Hope this make sense.
Please ask if there is anything not clear.
Sample of what I have,
| Group | Update_Overdue | Month | Week_Timestamp |
| Group1 | 0 | 1 Month | 1 |
| Group1 | 0 | 1 Month | 1 |
| Group1 | 0 | 1 Month | 1 |
| Group1 | 1 | 2 Months | 1 |
| Group2 | 1 | 3Months | 1 |
| Group2 | 1 | 2 Months | 1 |
| Group2 | 1 | 3Months | 1 |
| Group3 | 0 | 2 Months | 1 |
| Group3 | 1 | 3Months | 1 |
| Group3 | 0 | 1 Month | 1 |
| Group3 | 0 | 1 Month | 1 |
| Group3 | 1 | 1 Month | 1 |
| Group3 | 1 | 2 Months | 1 |
| Group2 | 1 | 3Months | 2 |
| Group1 | 0 | 2 Months | 2 |
| Group3 | 0 | 3Months | 2 |
| Group3 | 1 | 2 Months | 2 |
| Group1 | 1 | 3Months | 2 |
And need it like this
| Group | Month | Count | Week_Timestamp |
| Group1 | 1 Month | 3 | 1 |
| Group1 | 2 Months | 1 | 1 |
| Group2 | 2 Months | 1 | 1 |
| Group2 | 3Months | 2 | 1 |
| Group3 | 1 Month | 3 | 1 |
| Group3 | 2 Months | 2 | 1 |
| Group3 | 3Months | 1 | 1 |
| Group1 | 2 Months | 1 | 2 |
| Group1 | 3Months | 1 | 2 |
| Group2 | 3Months | 1 | 2 |
| Group3 | 2 Months | 1 | 2 |
| Group3 | 3Months | 1 | 2 |
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?
8 Replies
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
This definitely works and display the data like I want it to show , but if I can achieve the same via Measure or similar that would be great , as I'm using the current data in other queries , the solution you gave doesn't play well with my other graphs etc.
Hi Noob,
It's hard to give any other solution without more detail.. for instance you could acheve the same result in a measure that just counts the rows of the table:
Count = COUNTROWS(Table)
Then create a table with the columns in the values field:
Hi PowerBiNoob,
Besides the solution dearwatson posted by using Power Query to group by. You can also create DAX to a new table which display your espected result.
Click "New Table" under modeling on home page, type the following formula.Table = SUMMARIZE(Table3,Table3[Group],Table3[Week_Timestamp],Table3[Month],"COUNT",COUNTROWS(Table3))
Then right click group header->Sort Ascending, please see the following screenshot.
Best Regards,
Angelia
