Forum Discussion

Cbishop's avatar
Cbishop
Frequent Visitor
5 years ago
Solved

SUM function issue with referencing columns

Hi all,

 

I have created the following measure in my report

 

ERF Lower Threshold Achievement = if('Measure Table'[ERF Actuals]>('Measure Table'[ERF Baseline]*0.7),if('Measure Table'[ERF Actuals]<('Measure Table'[ERF Baseline]*0.85),'Measure Table'[ERF Actuals]-('Measure Table'[ERF Baseline]*0.7),('Measure Table'[ERF Baseline]*0.85)-('Measure Table'[ERF Baseline]*0.7)),0)
 
However, I need to be able to change the *0.7 based on the following values which are month related
 
April = 0.7
May = 0.75
June = 0.8
July to March = 0.85
 
These values are in a column but when I try to reference this column in the formula I get the 'sum function only accepts a column reference' error
 
Can anyone help?
 
Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    5 years ago

    Hi, Cbishop 

     

    Does it mean to use different multipliers according to different months?

    Create an auxiliary table that contains the baseline values for all months.
    Then use the month to establish a one-to-many relationship with the previous table, and use the RELATED function to create a new column. So you can use the sum function to refer to the new column.

    Hope this helps.

     

    Best Regards,
    Community Support Team _ Zeon Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    Hey Cbishop ,

     

    try to save the values in a variable before. That should solve your issue.

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     
    • Cbishop's avatar
      Cbishop
      Frequent Visitor
      5 years ago

      hi selimovd,

       

      many thanks for the response, however, I don't think this will work.

       

      I believe you are referring to setting the values as a slicer and then using selectedvalue function

       

      This would work when only looking at a specific month and making sure the correct value is selected within the slicer, however, when looking at the result of this calculation on a year to date basis I still need the months to be calculated using the correct month value

       

      Thanks

       

      Craig

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    5 years ago

    Hi, Cbishop 

     

    Does it mean to use different multipliers according to different months?

    Create an auxiliary table that contains the baseline values for all months.
    Then use the month to establish a one-to-many relationship with the previous table, and use the RELATED function to create a new column. So you can use the sum function to refer to the new column.

    Hope this helps.

     

    Best Regards,
    Community Support Team _ Zeon Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.