Simple Filter - Calculating Total Transaction Sales in Calculated Column

[ModernAchilles]

Hi,

This Simple Formula isnt working. Anyone have any ideas on how to to get the whole ticket transaction sum in a calculated column?

Thanks!

Ticket Total Sales = calculate([Net Revenue],
    filter(Sales,Sales[transactionid]=Sales[transactionid]))

[AlB · Accepted solution]

Hi @ModernAchilles

It's hard to say with the info you provide. You should show the tables in your data model and explain with an example based on your sample data what result you now get (error message? wrong number?) and what you would expect to get.

What is [Net Revenue], a measure? If so, please do show its code as it might be important to solve the issue.

I don't know what you mean by the whole ticket transaction sum. Although it's a shot in the dark, try this:

Ticket Total Sales =
CALCULATE (
    [Net Revenue],
    FILTER ( Sales, Sales[transactionid] = EARLIER ( Sales[transactionid] ) )
)

[ModernAchilles]

Hi @AlB

That is exactly what I need. I was looking for the whole ticket transaction for my rows and it wasn't calculating correctly. That earlier function is interesting... I'll have to dig into that one. 

Wow, thank you so much, I've been trying to find a solution all morning and last night. I really appreciate it!

Thanks again!

[AlB]

@ModernAchilles

No worries

What EARLIER does is give you the value in the previous row context. Note that since this is a calculated column, when FILTER( ) is executing you have  two row contexts:

- Row context 1 (outer) : the row context that you generally have in the current row of your table Sales

- Row context 2 (inner) : the row context that FILTER( ) creates when scanning the Sales table

EARLIER gives you access to the outer row context from inside FILTER( ). You scan Sales in FILTER and want to select only rows in which Sales[transactionid] in row context 2 is equal to the value of Sales[transactionid] in row context 1.

If you don't want to use EARLIER, which some people dislike, you can also obtain the same result with variables. This might actually be more intuitive:

Ticket Total Sales =
VAR _CurrentId = Sales[transactionid]
RETURN
    CALCULATE ( [Net Revenue], FILTER ( Sales, Sales[transactionid] = _CurrentId ) )