Help with Calculate a sum with a filter from another table

[Anonymous]

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

[v-ljerr-msft]

Hi @Anonymous,

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

[Anonymous]

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

[v-ljerr-msft · Accepted solution]

Hi @Anonymous,

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

[pattersonm8]

  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?

[Anonymous]

thanks that really worked!!!