Forum Discussion

David_S's avatar
David_S
New Member
3 years ago
Solved

Iteration inside a Measure with ADDCOLUMNS & CALCULATE

Hi BI Users,    Interesting question which may be lacking some conceptual understanding 🙄   I need to measure the Net Revenue Retention, we define it as the amount retained by a customer over a ...
Interesting Links
Need Help
Show and Tell
  • David_S's avatar
    David_S
    3 years ago

    Yourpattern suggestion is the rigth aproach, however, I was having conflicting issues with the ALLEXEPT when including it in the SUMMARIZE.  The solution was to create a CALCULATEDTABLE with the ALLEXCEPT and use that table in the SUMMARIZE

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
3 years ago

David_S It's hard to figure out what you need. It could be Measure Aggregation, a For Loop or a While Loop but not sure.

 

If it is a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.

 

For Loop

 

While Loop

David_S's avatar
David_S
New Member
3 years ago

Hi Greg,

 

Thanks for your responce, I actually tried that before but I am getting some weird results which I have tried to debug... but you know what the community thinks about SUMMARIZE, so I tried to go another way unsuccessful.