cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Wiredchop Regular Visitor
Regular Visitor

Issues using summarize in a measure

Hello all,

 

I have quite a complicated measure which isn't working as I'd expect.

 

Firstly, I'm creating a summary table in the measure. This table calculates a running average of the values I have in an existing table.

It does this by creating a  new column which is an average of the value on that row and the values following. I have tested the summarize calculation in a calculated column and this works very well.

 

However, when I put the same calculation in a measure and ask the measure to report (for example) the maximum value of the running average, things break down.

If I use the measure in a table to restrict the input then the measure value stays exactly the same -- no matter what filters I apply.

Is the summarize function limited to create a 'static' summary table? How can I get the measure to work.

I'm able to supply code if needed but thought I'd ask this general question first.

 

Thanks,

 

Wiredchop

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Issues using summarize in a measure

I wrote this article to solve a very similar sounding issue, get the MAX/MIN of an AVERAGE. Let me know if this works and if not, post your code:

 

https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

 


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

View solution in original post

3 REPLIES 3
Super User IV
Super User IV

Re: Issues using summarize in a measure

I wrote this article to solve a very similar sounding issue, get the MAX/MIN of an AVERAGE. Let me know if this works and if not, post your code:

 

https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

 


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

View solution in original post

Wiredchop Regular Visitor
Regular Visitor

Re: Issues using summarize in a measure

Fantastic,

fixed my formula by using maxx(summarize(...),...)  rather than calculate(max(...),summarize(...))

Is there an important reason why to use maxx in this situation?

 

Thanks for your help,

 

Wiredchop

Super User IV
Super User IV

Re: Issues using summarize in a measure

Well, essentially the "X" verssions of those are effectively doing a kind of table scan that evaluates the expression for each row and returns the largest or smallest numeric value out of the scan of those rows.

 

https://technet.microsoft.com/en-us/library/ee634576(v=sql.105).aspx

 


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

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.

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
Top Kudoed Authors