Forum Discussion
Measure calculating Conditional SUM according to Values from another Table Not Filtering As Expected
I have a Production Table with Tons and Meters and I have an Asset Table with a Site Column and an Asset Group Type Column. I've got a requirement for a measure to show Tons for certain Asset Group Types and show Meters for other Asset Group Types. I've been able to do a DAX query that does this, but if I try and slice on Site, my values of production (tons and meters) don't slice, they only show the total. There is a relationship between the Production Table and the Asset Table on Site. Here is my DAX query for the calculated measure on my Production table.
Tons/Meters =
IF (
VALUES ( Asset[AssetGroupType] ) IN { "AssetType1", "AssetType2" },
SUM ( Production[Tons] ),
SUM ( Production[Meters] )
)
Production Table:
| Site | Tons | Meters |
| Site1 | 100 | 5 |
| Site2 | 200 | 10 |
| Site3 | 300 | 15 |
Asset Table:
| Asset | AssetType | Site |
| Asset1 | AssetType1 | Site1 |
| Asset2 | AssetType2 | Site1 |
| Asset3 | AssetType3 | Site1 |
| Asset4 | AssetType3 | Site2 |
| Asset5 | AssetType1 | Site2 |
| Asset6 | AssetType2 | Site3 |
Please try
Tons/Meters = SUMX ( VALUES ( Asset[AssetGroupType] ), IF ( Asset[AssetGroupType] IN { "AssetType1", "AssetType2" }, SUM ( Production[Tons] ), SUM ( Production[Meters] ) ) )
2 Replies
- tamerj1
Community Champion
Please try
Tons/Meters = SUMX ( VALUES ( Asset[AssetGroupType] ), IF ( Asset[AssetGroupType] IN { "AssetType1", "AssetType2" }, SUM ( Production[Tons] ), SUM ( Production[Meters] ) ) ) - JohnShepherdAPD
Helper II
Try wrapping the sum inside the calculate function:
IF (
VALUES ( Asset[AssetGroupType] ) IN { "AssetType1", "AssetType2" },
CALCULATE(SUM ( Production[Tons] )),
CALCULATE(SUM ( Production[Meters] ))
)