cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ballist1x
Helper III
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

Hi @ballist1x,

 

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

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

 

Regards

View solution in original post

5 REPLIES 5
v-ljerr-msft
Microsoft
Microsoft

Hi @ballist1x,

 

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

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

 

Regards

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

Hi @ballist1x,

 

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

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

 

Regards

View solution in original post

  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?

 

thanks that really worked!!!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

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.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.