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
User Groups Public Preview

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.

March Update

Check it Out!

Click here to read more about the March 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.