Forum Discussion

danielfynesam2's avatar
danielfynesam2
New Member
3 years ago
Solved

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:

SiteTonsMeters
Site11005
Site220010
Site330015


Asset Table:

AssetAssetTypeSite
Asset1AssetType1Site1
Asset2AssetType2Site1
Asset3AssetType3Site1
Asset4AssetType3Site2
Asset5AssetType1Site2
Asset6AssetType2Site3
  • danielfynesam2 

    Please try

    Tons/Meters =
    SUMX (
        VALUES ( Asset[AssetGroupType] ),
        IF (
            Asset[AssetGroupType] IN { "AssetType1", "AssetType2" },
            SUM ( Production[Tons] ),
            SUM ( Production[Meters] )
        )
    )

2 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion

    danielfynesam2 

    Please try

    Tons/Meters =
    SUMX (
        VALUES ( Asset[AssetGroupType] ),
        IF (
            Asset[AssetGroupType] IN { "AssetType1", "AssetType2" },
            SUM ( Production[Tons] ),
            SUM ( Production[Meters] )
        )
    )
  • Try wrapping the sum inside the calculate function:

    IF (
    VALUES ( Asset[AssetGroupType] ) IN { "AssetType1", "AssetType2" },
    CALCULATE(SUM ( Production[Tons] )),
    CALCULATE(SUM ( Production[Meters] ))
    )