cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tati1292
New Member

How to sum up / group up values on a table without considering a measure formula

Hi,

 

I would like to know if there is a way I can get rid off the internal measure formula to just group/sum up items on a table in Power BI.

 

On the first place, I created the formula below in order to get the total savings by project (considering that each project could contain more than one sub-category/row):

2022 Savings by Project = CALCULATE(CALCULATE(SUM('New Provalido Download'[2022 savings New P]),FILTER(ALLSELECTED('New Provalido Download'),'New Provalido Download'[2022 savings New P]=MAX('New Provalido Download'[2022 savings New P]))),ALLEXCEPT('ID Table','ID Table'[Project ID/Project Name]))

 
Nevertheless, when I create a table to summarize by sub-category, the total by sub-category shows the MAX value considering all the projects and not SUM of the projects in that sub-category as it should be. For example, projects in Logistics & Freight (highlighted in yellow) are showing incorrectly a total of 761,026, instead of 332,650 as it should be:

tati1292_1-1669816137140.png

 

Many thanks in advance!

 

1 ACCEPTED SOLUTION
v-tangjie-msft
Community Support
Community Support

Hi @tati1292 ,

 

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

vtangjiemsft_0-1669859474166.png

(2) We can create a measure. 

 

2022 Savings by Project = CALCULATE(SUM('Table'[2022 savings New P]),FILTER(ALLEXCEPT('Table','Table'[Workstream Sub Cat],'Table'[Type2]),'Table'[2022 savings New P]))

 

(3) Then the result is as follows.

vtangjiemsft_1-1669859509131.png

You can refer to the following documents that may be helpful to you:

Solved: Measure to calculate SUM of Values based on Groupi... - Page 2 - Microsoft Power BI Communit...

Grouping Measures in Matrix Table - Power BI - Enterprise DNA Forum

How to Perform Aggregation and Summarization in DAX — DAX in Power BI — Chapter 4 | by Arpita Ghosh ...

 

Best Regards,

Neeko Tang

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
tati1292
New Member

Hi Neeko,

Thank you so much for your reply. I tried your formula for 2022 Savings by Project but it's excluding the Project ID so it's not giving me the expected result by project I was getting with my previous formula. What I was trying on the first place is getting a SUMIF by Project ID for 2022 Savings since each Project ID could have more than one sub-category depending on the year of the savings as you can see below in Excel:

tati1292_2-1669987101483.png

My goal in Power BI is to get the CY column: =IF(CX8755="Buy-In",CV8755-SUMIF(C:C,C8755,CU:CU),IF(AND(CX8755="Compete/New Buy-In",'PowerBI Download - USD - 01 201'!CV8755=0),0,'PowerBI Download - USD - 01 201'!CV8755-'PowerBI Download - USD - 01 201'!CU8755))

 

So the two formulas I created in Power BI for this were the following:

1. 2022 Savings by Project = CALCULATE(CALCULATE(SUM('New Provalido Download'[2022 savings New P]),FILTER(ALLSELECTED('New Provalido Download'),'New Provalido Download'[2022 savings New P]=MAX('New Provalido Download'[2022 savings New P]))),ALLEXCEPT('ID Table','ID Table'[Project ID/Project Name]))
 
2. 2023vs2022 FINAL = IF(AND(SUM('New Provalido Download'[2022 savings New P])=0,SUM('New Provalido Download'[2023 savings New P])=0),0,IF(SUM('New Provalido Download'[2022 savings New P])=0,SUM('New Provalido Download'[2023 savings New P])-[2022 Savings by Project],IF(AND(SUM('New Provalido Download'[2023 savings New P])=0,[2022 Savings by Project]<>0),0,SUM('New Provalido Download'[2023 savings New P])-SUM('New Provalido Download'[2022 savings New P]))))
 
By doing this I got the correct result by Project ID but when I want to get the subtotal by sub category I get it incorrectly since it's performing the same formula as it is doing by Project ID instead of just summing up all the projects within the sub category:
tati1292_1-1669986970337.png

 

Thank you!

Tatiana

v-tangjie-msft
Community Support
Community Support

Hi @tati1292 ,

 

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

vtangjiemsft_0-1669859474166.png

(2) We can create a measure. 

 

2022 Savings by Project = CALCULATE(SUM('Table'[2022 savings New P]),FILTER(ALLEXCEPT('Table','Table'[Workstream Sub Cat],'Table'[Type2]),'Table'[2022 savings New P]))

 

(3) Then the result is as follows.

vtangjiemsft_1-1669859509131.png

You can refer to the following documents that may be helpful to you:

Solved: Measure to calculate SUM of Values based on Groupi... - Page 2 - Microsoft Power BI Communit...

Grouping Measures in Matrix Table - Power BI - Enterprise DNA Forum

How to Perform Aggregation and Summarization in DAX — DAX in Power BI — Chapter 4 | by Arpita Ghosh ...

 

Best Regards,

Neeko Tang

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.