Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi Anonymous,

     

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

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

     

    Regards

5 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi Anonymous,

     

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

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

     

    Regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        v-ljerr-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi Anonymous,

         

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

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

         

        Regards