Forum Discussion

MDR's avatar
MDR
Frequent Visitor
3 years ago
Solved

Conditional SUM

I used the following DAX to return total "Approved" amounts across all datanameshorts in my dfact table. When datanameshort is either "SBG" or "SBG10" the total of "Approved" and "Paid" amounts must ...
" Slicer"
"CALCULATE"
multiple filters
sum
  • johnt75's avatar
    johnt75
    3 years ago

    The problem is that there is more than value for data name short, so SELECTEDVALUE will return blank.

    Try

    ApprovedAmount =
IF (
    NOT ISEMPTY ( INTERSECT ( VALUES ( DFACT[DATANAMESHORT] ), { "SBG", "SBG10" } ) ),
    CALCULATE (
        SUM ( DFACT[AMOUNT] ),
        DFACT[DATANAMESHORT]
            IN { "SBG", "SBG10" }
                && DFACT[STATUS]
                    IN { "Approved", "Paid" } && NOT ISBLANK ( DFACT[GEONAME] )
    ),
    CALCULATE ( SUM ( DFACT[AMOUNT] ), DFACT[STATUS] = "Approved" )
)
MDR's avatar
MDR
Frequent Visitor
3 years ago

I have it in a table with datname as below:

 

Small Business Disaster Recovery Grants represents both "SBG" and "SBG10".

I've used a similar aggregated measure in a card without any issues but in the table instead of getting the sum of "Approved" and "Paid" for "Small Business Disaster Recovery Grants" I am getting only "Approved".

The aggregated mesure is as follows:

CALCULATE(SUM(DFACT[AMOUNT]), FILTER(DFACT, CONTAINSSTRING(DFACT[DATANAMESHORT],"SBG") && DFACT[STATUS] IN {"Approved", "Paid"} && DFACT[GEONAME] <> BLANK())
johnt75's avatar
johnt75
Super User
3 years ago

The problem is that there is more than value for data name short, so SELECTEDVALUE will return blank.

Try

ApprovedAmount =
IF (
    NOT ISEMPTY ( INTERSECT ( VALUES ( DFACT[DATANAMESHORT] ), { "SBG", "SBG10" } ) ),
    CALCULATE (
        SUM ( DFACT[AMOUNT] ),
        DFACT[DATANAMESHORT]
            IN { "SBG", "SBG10" }
                && DFACT[STATUS]
                    IN { "Approved", "Paid" } && NOT ISBLANK ( DFACT[GEONAME] )
    ),
    CALCULATE ( SUM ( DFACT[AMOUNT] ), DFACT[STATUS] = "Approved" )
)
  • MDR's avatar
    MDR
    Frequent Visitor
    3 years ago

    Thanks heaps johnt75. it worked