cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerBiNoob Frequent Visitor
Frequent Visitor

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,

GroupUpdate_OverdueMonthWeek_Timestamp
Group101 Month1
Group101 Month1
Group101 Month1
Group112 Months1
Group213Months1
Group212 Months1
Group213Months1
Group302 Months1
Group313Months1
Group301 Month1
Group301 Month1
Group311 Month1
Group312 Months1
Group213Months2
Group102 Months2
Group303Months2
Group312 Months2
Group113Months2

 

And need it like this

 

GroupMonthCountWeek_Timestamp
Group11 Month31
Group12 Months11
Group22 Months11
Group23Months21
Group31 Month31
Group32 Months21
Group33Months11
Group12 Months12
Group13Months12
Group23Months12
Group32 Months12
Group33Months12
1 ACCEPTED SOLUTION

Accepted Solutions
dearwatson Member
Member

Re: 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?

 

 

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte

View solution in original post

8 REPLIES 8
Highlighted
dearwatson Member
Member

Re: Grouped Average

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

Capture.PNG

 

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.

Capture2.PNG

 

click OK and Viola!

Capture.PNG

 

I think this is right... let me know.

 

Cheers

 Greg

 

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte
PowerBiNoob Frequent Visitor
Frequent Visitor

Re: Grouped Average

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.

 

dearwatson Member
Member

Re: Grouped Average

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:

Capture.PNG

 

 

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte
PowerBiNoob Frequent Visitor
Frequent Visitor

Re: Grouped Average

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).

Avg.png

 

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?

dearwatson Member
Member

Re: 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?

 

 

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte

View solution in original post

Microsoft v-huizhn-msft
Microsoft

Re: Grouped Average

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.

1.PNG

Best Regards,
Angelia

PowerBiNoob Frequent Visitor
Frequent Visitor

Re: Grouped Average

Thanks dearwatson, sorry for the delay in reply. Was Out of the office for a while.

 

Cheers

Anonymous
Not applicable

Re: Grouped Average

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 🙂

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors