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

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 459 members 4,782 guests
Recent signins: