Forum Discussion

Scholes's avatar
Scholes
Frequent Visitor
2 years ago

Column (sub) total not correct from measure

Hello.

 

I have created a measure which shows the monhtly value between two dates, a start and an end date.

 

Current Rev per monht = CALCULATE(
    SUM('Rev contract'[Rev per Month]),
    FILTER(CALCULATETABLE('Rev contract',ALL('Calendar')),
        'Rev Contract'[Start]<=MIN('Calendar'[Date])&&
        'Rev Contract'[End]>MIN('Calendar'[Date])
                ))

 

 

As you see the row calculation works but my matrix does not show a column grand total for summing up all the different periods/months.

What do I need to change to my measure?

 

Best regards,

 

 

 

 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    Scholes , Try like

     

    Current Rev per monht = CALCULATE(
    SUM('Rev contract'[Rev per Month]),
    FILTER(CALCULATETABLE('Rev contract',ALL('Calendar')),
    'Rev Contract'[Start]<=Max('Calendar'[Date])&&
    'Rev Contract'[End]>=MIN('Calendar'[Date])
    ))

    • Scholes's avatar
      Scholes
      Frequent Visitor
      2 years ago

      amitchandak 

      Ok I changed the formula when I add year as a column I see now a total  but this total corresponds with the value of the first month, so there is no sum of the individual periods, same as the total value per year is not calculated.

       

       

  • Scholes's avatar
    Scholes
    Frequent Visitor
    2 years ago

    amitchandak 

    Ok I managed to get annual totals by combining the SUMX funciton  

    Rev per Month = 
SUMX(VALUES(Calendar[Month]),[Current Rev per month]
)

    This works only on an annual level. Not for multiple years. I would expect the total for multiple years