Using a SUMIF or CALCULATE

[Tucker]

Hey Guys,

I'm new to DAX and struggling with a formula, I have a table coming to PBI from a DB view. In the table I have a calculated column that takes the value of a SKU (Product) and multiples by the volume (Sales) to give total value. (these are OK as simple DAX) 

Each row then belongs to an invoice date, the invoicve date is then joined to a DimDate tbl in PBI. Which I could use or I can convert the date column to a MMYYYY format and use that (see below as this is what I've done in Excel).

In excel I'd use this =SUMIFS(L:L,D:D,D4,F:F,F4) where L is the data to sum, D is the date(MM-YYYY) and F is a supplier id. This would give a new column that sums the total based on the 2 criteria. I have 2 lots of data, a source and target value which are both sum'd up by the same volume, these are then subtracted to give a variance which in turn is divided by the total to show a %

I'm sure it's a CALCULATE formula but I can't get it to work, the black columns are my DB data and the red are what I'm trying to calc in PBI

Thanks in advance for you any help TB

[CoreyP · Accepted solution]

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?

[MFelix · Accepted solution]

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

[Tucker]

MFelix

Thanks so much for taking the time to reply with seach detail, this is a great explaination thanks and very much appreciated

[CoreyP · Accepted solution]

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?

[Tucker]

CoreyP,

Thanks for the solution it works perfectly along with MFelix I'm spoilt for choice, thanks also for talking the time to comment back