Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Taking the average of the sum of values per date

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.

 

BP.PNG

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

Try a new measure like

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

View solution in original post

7 REPLIES 7
amitchandak
Super User
Super User

Try a new measure like

averagex(summarize(Table[Date],"_Sum",sum(Table[Value])),[_Sum])
Anonymous
Not applicable

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?

@Anonymous , one of the way

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

Anonymous
Not applicable

FANTASTIC as well always @amitchandak , thank you!

Anonymous
Not applicable

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! 

Anonymous
Not applicable

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

Summarize can be used to create a new table

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.