cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ModernAchilles Regular Visitor
Regular Visitor

Simple Filter - Calculating Total Transaction Sales in Calculated Column

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]))

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Simple Filter - Calculating Total Transaction Sales in Calculated Column

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] ) )
)

 

 

 

3 REPLIES 3
Highlighted
Super User
Super User

Re: Simple Filter - Calculating Total Transaction Sales in Calculated Column

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 Regular Visitor
Regular Visitor

Re: Simple Filter - Calculating Total Transaction Sales in Calculated Column

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!

 

Super User
Super User

Re: Simple Filter - Calculating Total Transaction Sales in Calculated Column

@ModernAchilles

No worries Smiley Happy

 

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 ) )