Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Calculate and filter help!

Hi i need help correcting a formula in a measure.

 

im tryign to calculate the total revenue based upon another table that has a calculated number of months.

 

i want to get the total revenue in all imnstances where the number of months is 12 (or in my calculation less than 13)

 

12 Month Revenue = calculate(SUM(H12015[Final Amount (£)]), FILTER(ALL('Start Date'[Length of Tenure Absolute]<13),0))

 

Can anyone tell me where i am going wrong?

 

i get the folloiwng error:

 

A single value for column 'Length of Tenure Absolute' in table 'Start Date' 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.

Need Help

3 Replies

  • JoHo_BI's avatar
    JoHo_BI
    Responsive Resident
    8 years ago

    Hi Anonymous, 

     

    The ALL parameter takes a take to start, so you'll need something like: 

     

    12 Month Revenue = calculate(SUM(H12015[Final Amount (£)]), FILTER(ALL('Start Date'), 'Start Date'[Length of Tenure Absolute]<13),0)

     

    Hope that helps! 

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      thanks unforutnately it didnt work,

       

       i get the error:  The true/false expression doesnt specify a column.

       

      Also, if i needed to get it to average the final amount would this be right?

       

      So calculate the sum of the amount averaged by the number of people with a tenure of less than 13  months.

       

      12 Month Revenue = calculate(SUM(average(H12015[Final Amount (£)])), FILTER(ALL('Start Date'), 'Start Date'[Length of Tenure Absolute]<13),0)

      • JoHo_BI's avatar
        JoHo_BI
        Responsive Resident
        8 years ago

        Can you post some sample data? It'll be easier to answer if we see how your datais structured.

         

        Thanks!