I need help calculating totals based on another columns value

[ANC]

Hi everyone,

Hoping someone here can help me finish off the start I've made on this calculation.

#TEST =

var _AD = CALCULATE(

    SUM('Table'[Transaction_Type_Flag]),

    'Table'[Transaction_Type] = "AD",

    FILTER('Table' ,

        SELECTEDVALUE('Table'[ID]) = 'Table'[ID]))

return

CALCULATE(

  SUMX(

  SUMMARIZECOLUMNS(

    'Table'[ID],

    'Table'[Transaction_Value])

    ,

    IF(_AD <> 0 , SUM('Table'[Transaction_Value]) , 0)),'Table'[Transaction_Type] = "TR")

The outcome I am hoping for is for the total in the table visual to sum the transaction value for all IDs where the sum of transaction_type_flag is greater than 0. In this example it should show 125, the measure appears to be working at a row level but not at a total level.

The concept is that if an ID has been part of "AD" even once within a filtered date range, then I would like to see the sum of transaction_value, but if it has not been included in "AD" during that time period then it to return blank. The total needs to act as if it would in Excel, where it will sum only the values visible in the table visual.

I have tried other version of this sum that again I can get to work at a row level, but the total then takes a sum of all transaction_value where transaction_type = "TR", rather than a sum of all transaction_value where sum transaction_type_flag > 0.

This has to be done in a measure rather than a table/by filtering as there are other fields in the table that I need to display.

Thanks in advance for any help!

[Wilson_ · Accepted solution]

Hi ANC,

Try the below:

#TEST = 
-- returns all IDs with flag values other than 0
VAR Filtered =
SELECTCOLUMNS (
    FILTER (
        'Table',
        'Table'[Transaction Type Flag] > 0
    ),
    "ID", 'Table'[ID]
)
-- sums transaction value for all IDs that have at least one flag value other than 0
VAR Result =
CALCULATE (
    SUM ( 'Table'[Transaction Value] ),
    'Table'[ID] IN Filtered
)

RETURN Result

I took the same general approach as you (ie: a var to isolate the IDs, then returning calculation based on those IDs).

This was my resulting visual:

(Also, thank you for sharing your raw data. That helped tremendously. For future reference, please share it as a table instead of a screenshot. That way, it'll be easier for people helping to re-create your dataset. :D)

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

[ANC]

Thank you Wilson_!

This has worked perfectly! I knew I was somewhere on the right tracks, I was just missing the structure, so thank you!

And thanks for the tip on posting tables, this is my first time using this forum and couldn't quite work out the best way to do things but I've spotted the insert table icon now, I will be sure to use this next time!

Thanks once again!

[Wilson_]

You're welcome. 😄

And no worries, just an FYI to help you remove friction for anybody that sees your future posts.