Forum Discussion

AlBud's avatar
AlBud
Frequent Visitor
3 years ago
Solved

New Measure - Multiple sum if's

Hi,

 

I'm trying to create a new measure in PowerBI Desktop that can sum multiple rows of a colum based on 3 criteria in another column in the same table "G_LEntries".

 

My "G_LEntries" table is as per below, and I only want to sum "2010", "2011" and "2025".

 

I will have a slicer on the visual, so the measure must be able to be filtered by date. I have a relationship setup between my date table and the "G_LEntries" table for the slicer for this.

 

Posting DateG_L_Account_NoAmount
15/09/202220105
01/12/202220251.75
07/06/202320109
01/12/202220113
15/02/202320110.33
03/05/20222010100
01/01/2021202999
12/01/2022202936
09/06/202320251
23/02/202220110.07
23/02/202220290.88
12/01/2022202550
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi AlBud 

    Whether you want to calculate the sum corresponding to 2011, 2010, 2015, or the sum of these three years separately, the following scenario is to calculate the corresponding sum of the corresponding quantities for these three years.

     

    Measure = var a=SUMMARIZE(FILTER(ALLSELECTED('G_LEntries'),[G_L_Account_No] in {2010,2011,2025}),[G_L_Account_No],"Sum",CALCULATE(SUM(G_LEntries[Amount])))
return IF(SELECTEDVALUE(G_LEntries[G_L_Account_No]) in {2010,2011,2025},MAXX(FILTER(a,[G_L_Account_No]in VALUES(G_LEntries[G_L_Account_No])),[Sum]),SUM(G_LEntries[Amount]))

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi AlBud 

    Whether you want to calculate the sum corresponding to 2011, 2010, 2015, or the sum of these three years separately, the following scenario is to calculate the corresponding sum of the corresponding quantities for these three years.

     

    Measure = var a=SUMMARIZE(FILTER(ALLSELECTED('G_LEntries'),[G_L_Account_No] in {2010,2011,2025}),[G_L_Account_No],"Sum",CALCULATE(SUM(G_LEntries[Amount])))
return IF(SELECTEDVALUE(G_LEntries[G_L_Account_No]) in {2010,2011,2025},MAXX(FILTER(a,[G_L_Account_No]in VALUES(G_LEntries[G_L_Account_No])),[Sum]),SUM(G_LEntries[Amount]))

     

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • AlBud's avatar
      AlBud
      Frequent Visitor
      3 years ago

      Hi Yolo,

       

      This works, thanks!