Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

DAX SUM with GROUP BY?

I'm needing help with two seemingly easy DAX calculations, but I'm not able to get anything working.  I'm trying to create a measure called [AMOUNT2] that takes the sum of [AMOUNT] (this is a column in my table), grouped by [ID], [SOME_ID], and [ANOTHER ID].  Once [AMOUNT2] as been caculated, I wanted to create a measure called [MAX_OF_AMOUNT2] that takes the max of [AMOUNT2] and is grouped by [ID]. 

 

Any ideas or direction would be most helpful! I've provided a visual example of the data table below for reference. 

 

                              

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous 

    Create two measures

    Measure = CALCULATE(SUM(Table1[amount]),ALLEXCEPT(Table1,Table1[id],Table1[some_id],Table1[another_id]))

Measure 2 = MAXX(ALLEXCEPT(Table1,Table1[id]),[Measure])

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Anonymous 

    Create two measures

    Measure = CALCULATE(SUM(Table1[amount]),ALLEXCEPT(Table1,Table1[id],Table1[some_id],Table1[another_id]))

Measure 2 = MAXX(ALLEXCEPT(Table1,Table1[id]),[Measure])

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • dax_powerbi's avatar
      dax_powerbi
      Frequent Visitor
      3 years ago

      Hi Maggie, would be nice if you can put an explanation of what the two measures is doing so that a newbie like myself can understand what is going on. Especially the function AllExcept, I still find it difficult to undestand 🙂
      Thank you