cancel
Showing results for
Did you mean:
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

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

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

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

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

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

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

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

Announcements

#### Community News & Announcements

Get your latest community news and announcements.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 183 members 1,763 guests
Recent signins: