cancel
Showing results for
Did you mean:
Helper III

## Help with Calculate a sum with a filter from another table

Hi, i am trying to work out how i can calculate a total revenue for orders based upon a filter from another table that has a 2 way relationship;

TotalRevenue = CALCULATE(SUM(H12015[Final Amount (£)]), FILTER(Plan;Plan[PM]=PPM - CC(PM]))

can anyone take a look at that and tell me what is wrong? on one table i have customer, order value and product categories, i.e sandwich, burger etc... thats linked to a PPM so i can select PPM - CC on a drop down on a filter and it filters the revenue number to the products in that PPM's remit.

i want to be able to show ona  table the total revenue and also the total revenue for that PPM on an x axis on a table so that a comparison can be made...

1 ACCEPTED SOLUTION
Microsoft

Hi @ballist1x,

Could you try the formula below to see if it works?

```TotalQuantity =
CALCULATE (
SUMX ( H12015, [Final Amount (£)] ),
FILTER ( ALL ( Plans[Partner Manager] ), Plans[Partner Manager] = "PPM - CM" )
)```

Regards

5 REPLIES 5
Microsoft

Hi @ballist1x,

Could you try using the formula below to create a measure to see if it works in your scenario?

```TotalRevenue =
CALCULATE (
SUM ( H12015[Final Amount (£)] ),
FILTER ( ALL ( Plan ), Plan[PM] = "PPM - CC" )
)
```

Regards

Helper III

Hi i have tried:

TotalQuantity = CALCULATE(SUMX(H12015[Final Amount (£)], FILTER(all(Plans[Partner Manager]), Plans[Partner Manager] = "PPM - CM")))

But i get an error

:A single value for column 'Final Amount (£)' in table 'H12015' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

There are many rows in H12015

Final Amount (£) is a sum

Microsoft

Hi @ballist1x,

Could you try the formula below to see if it works?

```TotalQuantity =
CALCULATE (
SUMX ( H12015, [Final Amount (£)] ),
FILTER ( ALL ( Plans[Partner Manager] ), Plans[Partner Manager] = "PPM - CM" )
)```

Regards

Helper I

this works with how you hard coded = "specific text", but how do you right DAX for want it to return for all rows with that same "specific text" from that row?

Helper III

thanks that really worked!!!

Announcements