cancel
Showing results for
Search instead 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!!!

## Helpful resources

Announcements

#### Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

#### Check it Out!

Click here to read more about the March 2021 Updates!

#### The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors