Using a SUMIF or CALCULATE

Hey Tucker,

I'm relatively new to Power BI and DAX as well, but I've been learning steadily for months now.

The most important thing for me to learn was the difference between measures and calculated columns. So let me just throw out a simple explanation of the two. 

Use calculated columns when you're wanting to perform a function ROW by ROW. Meaning, you're wanting to perform the same calculation for each ROW of the table. 

Use measures when you're wanting to perform a function on a COLUMN. eg) aggregating values in the same column. (It's sounds counter intuitive, right? If I want to calculate a column, i should use a calculated column.. haha nope)

Back to your issue. You want to perform a SUM of only values in the same column, that have specific criteria in other columns, right? 

So, I would do a new measure:

Measure =

                  CALCULATE(

                                      SUM( 'Sales Table'[Total Sales] ) ,

                                      Filter/Criteria 1 ,

                                      Filter/Criteria 2 ,

                                      )

Is this about what you're wanting? Or did I not understand you correctly?

Hi Tucker,

In DAX you don't need to have a calculation based on filters since the measures are based on context, meaning that a simple SUM calculation is made based on the different context (columns / slicers / ...) that you have in your visuals.

In this case you need to calculate the following measures:

Source Amount = SUMX(Sales; Sales[SourceInv] * Sales[Quantity] )

Target Amount = SUMX(Sales; Sales[TargetPSI] * Sales[Quantity] )

Variance = [Source Amount] - SUM(Sales[TargetPSI])

Variance % = [Sum Variance] / [Sum Source]


Sum Source = CALCULATE([Source Amount];ALL(Sales))

Sum Target = CALCULATE([Target Amount];ALL(Sales))

Sum Variance = CALCULATE([Variance]; ALL(Sales))

This measure are based in context and the last ones is were you make overlap the filters to give you the results for all the table.

Althoug CoreyP is correct regarding there is a difference between calculated columns and calculated measures, the fact that we wnat a row by row calculation doesn't need to be made by a column you have the X functions that make the interaction row by row, like the SUMX that I use bute there are a lot more.

As you can see above the calculations are made with your data and the columns give the expected results, however as you can see in the cards on the bottom if you remove context from a simple measure like the [Source Amount] that calculates result row by row you get the exact same result the [Sum source] that uses a CALCULATED function to filter out information and give you the sum of Source amount for ALL rows in your table.

 One best practice is that if you can use a measure don't use a calculated column since columns add complexity to your model and size.

Also be aware that making a SUMX or a ALL formula based on a full table is not a good practice you should use the columns that you will need on your visuals for performance and context.

In attach the PBIX file I used for this answer.

Regards,

MFelix