Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Taking the average of the sum of values per date

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

spenot09

Helper IV

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-26-2020
02:18 AM

Hi Community! I have a table where I would like to sum up the values per individual date (i.e. move from the table on the left to the table in the middle below) and then take the daily average of those values (value on the right below). The "Date", "Segment 1", and "Segment 2" dimensions are filtered so the solution should respond to any filtering required. TO CLARIFY: I would only like the average value at the end, no need for the intermediate table I just included it for the sake of explanation.

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

**Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!**

**My Recent Blog -**Week is not so Weak Connect on Linkedin

amitchandak

Super User IV

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-26-2020
02:40 AM

Try a new measure like

`averagex(summarize(Table[Date],"_Sum",sum(Table[Value])),[_Sum])`

Proud to be a Super User!

7 REPLIES 7

Highlighted

**Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!**

**My Recent Blog -**Week is not so Weak Connect on Linkedin

amitchandak

Super User IV

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-26-2020
02:40 AM

Try a new measure like

`averagex(summarize(Table[Date],"_Sum",sum(Table[Value])),[_Sum])`

Proud to be a Super User!

Highlighted
##

spenot09

Helper IV

Re: Taking the average of the sum of values per date

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-26-2020
03:36 AM

averagex(summarize('Table', [Date],"_Sum",sum(Table[Value])),[_Sum])

Small change as the format wasn't quite correct before I believe. Huge thank you though!

Highlighted
##

mkirubakaran

Helper II

Re: Taking the average of the sum of values per date

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-26-2020
03:40 AM

@amitchandak - It worked beautifully. Is there a way, I can add the grouped value in a new table, so that I can maintain a history of the data.

Highlighted
##

**Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!**

**My Recent Blog -**Week is not so Weak Connect on Linkedin

amitchandak

Super User IV

Re: Taking the average of the sum of values per date

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-26-2020
04:57 AM

Summarize can be used to create a new table

Proud to be a Super User!

Highlighted
##

spenot09

Helper IV

Re: Taking the average of the sum of values per date

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-28-2020
11:23 AM

Hi @amitchandak thank you again hugely for your help. I have one more question related to this. What if I wanted the average to be calculated on only a specific category (e.g. where Segment 2 = "A" or "B"). Where would this filter statement go for the rest of the summarize to work?

Highlighted
##

**Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!**

**My Recent Blog -**Week is not so Weak Connect on Linkedin

amitchandak

Super User IV

Re: Taking the average of the sum of values per date

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-28-2020
11:27 AM

@spenot09 , one of the way

averagex(summarize(filter(Table, Table[Segment 2] in {"A","B"}), Table[Date],"_Sum",sum(Table[Value])),[_Sum])

Proud to be a Super User!

Highlighted
##

spenot09

Helper IV

Re: Taking the average of the sum of values per date

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-29-2020
06:43 AM

FANTASTIC as well always @amitchandak , thank you!

Announcements

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors

User | Count |
---|---|

371 | |

133 | |

84 | |

79 | |

79 |

Top Kudoed Authors

User | Count |
---|---|

492 | |

204 | |

156 | |

120 | |

105 |