Forum Discussion

shahidalaila23's avatar
shahidalaila23
Frequent Visitor
3 years ago
Solved

how to get total amount excluding specific data

I need to get net amt for loc_group with loc_group 1034 total amt is excluding the amt that was send to other loc_group.    the situation as below:  below table represent loc_group and supp_code t...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    I am not sure how your data model looks like, but please check the below picture and the attached pbix file.

     

     

     

    Amount total expected result: =
VAR _expected =
    SUM ( Purchase[amt] )
        - CALCULATE (
            SUM ( Purchase[amt] ),
            FILTER ( ALL ( Purchase ), Purchase[supp_code] = "1034" )
        )
RETURN
    IF (
        HASONEVALUE ( loc_group[loc_group] ),
        SWITCH (
            SELECTEDVALUE ( loc_group[loc_group] ),
            "1034", _expected,
            SUM ( Purchase[amt] )
        ),
        _expected
    )

     

Jihwan_Kim's avatar
Jihwan_Kim
Super User
3 years ago

Hi,

I am not sure how your data model looks like, but please check the below picture and the attached pbix file.

 

 

 

Amount total expected result: =
VAR _expected =
    SUM ( Purchase[amt] )
        - CALCULATE (
            SUM ( Purchase[amt] ),
            FILTER ( ALL ( Purchase ), Purchase[supp_code] = "1034" )
        )
RETURN
    IF (
        HASONEVALUE ( loc_group[loc_group] ),
        SWITCH (
            SELECTEDVALUE ( loc_group[loc_group] ),
            "1034", _expected,
            SUM ( Purchase[amt] )
        ),
        _expected
    )