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
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:
Yeah I understand what you saying, The end result we want to achieve for the stacked bar chart is displaying the Avg for the months( 1 Month,2 Months,3 Months) where the horizontal axis will be the "End of the week" date. The way the data is being captured is daily the files get loaded and timestamp added, the data is a combination of muliple groups each with a count of Over_Due files broken up into ( 1 Month,2 Months,3 Months).
So in the screenshot you will see , I added all the records (per day ,per group,) that is grouped by the Months ( 1 Month,2 Months,3 Months) and the the avg is worked out. Currently it is mostly being done in the backend on Tsql , but we want to rather load the whole table (Select * From ... ) into Power Bi and do the calculations and aggregations etc, in Power Bi and also to minimise the amount of datasources we have currently. Is this a good way of doing it?
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?