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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
AlBud
Frequent Visitor

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
1 ACCEPTED SOLUTION
v-xinruzhu-msft
Community Support
Community Support

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]))
 

 

vxinruzhumsft_0-1687488571953.png

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.

 

View solution in original post

2 REPLIES 2
v-xinruzhu-msft
Community Support
Community Support

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]))
 

 

vxinruzhumsft_0-1687488571953.png

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.

 

Hi Yolo,

 

This works, thanks! 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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