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 total amt. Only 1034 will buy item c from supp_code S-03 and will send 1 to other loc_group.

 

This is purchase table

 

and this is transfer table

I have both table in power BI

below is the expected outcome: 

so far I cannot seperate the amt from 1034 in powerBI 

 

thank you;

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
    )

     

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper 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
    )