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" )
)
johnt75's avatar
johnt75
Super User
3 years ago

How are you using the measure? Is it in a visual which includes the data name short column or is it just in aggregation ?

  • 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