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

[tati1292]

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:

Many thanks in advance!

[tati1292]

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:

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:

Thank you!

Tatiana

[v-tangjie-msft · Accepted solution]

Hi @tati1292 ,

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

(1) This is my test data. 

(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.

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.