IF Function Assistance

[DJsummers]

Hi

In the below table, I have a Standard weight and I have a parent age. If the parent age is below 35, then I want the standard weight to be reduced by 5% (multiplied by 0.95)

I tired doing it as a measure to begin with but when I typed in IF, it would only let me select the BaseFIlterView table and not the CumulativeweightsView2 table, so I have attempted to do it by adding a new column into CumulativeweightsView2.

I would like advice on what is wrong with my formula there and why am I getting the error message 'The SUM function only accepts a column reference as an argument'?

Many Thanks in advance!

[PhilipTreacy]

Hi @DJsummers 

Download example PBIX file.

Thanks for the data but it's not made things any clearer I'm afraid.  When I look at Housename 1 (is this supposed to be the same as Housename 01?), on Standard Day 10, the Max Record Weight is 0.38 (your image shows 0.29), and the Avg Parent Age I see is 43.74 (your image shows 29).  So I'm not sure if I've got the same data as you??

I don't think I fully understand what it is you are trying to do.  You say that you'd expect to see 0.305 as the Target for Day 10 - why?  How do you calculate that?

In your initial question you said I have a Standard Weight and a Parent Age - but the image shows Max of Standard Weight and Average Parent Age.

Can you please rewestate what it is you are tryinmg to do and show the  expected result.

Please use the PBIX file I linked to above which contains the data you sent me.  You'll need to change the Source step to load from an XLSX file on your PC.

Regards

Phil

[DJsummers]

@PhilipTreacy Apologies for the late response, I really appreciate the help you have been. I've accepted your first answer as the solution as it actually resolved what I was asking originally! I have since found that there is an issue with my SQL View which isn't showing the latest Parent Age, it was adding all the Parent Ages together since the history of time so that's why it wasn't showing the result I was expecting.

Anyway! Hope you're having an excellent Day.

Dan

[PhilipTreacy]

Hi @DJsummers 

Try copying the table and then pasting it into Excel. 

Save the workbook to OneDrive, Drop box etc where I can download it.

Regards

Phil

[DJsummers]

@PhilipTreacy Morning! (Afternoon?) I'm not being given the copy table option, I am doing a direct query if that makes any difference.

Anyhow, I have copied a sample of both tables via transform data, although the new column you helped me isn't showing. 

https://appliedgroupltd-my.sharepoint.com/:x:/g/personal/daniel_summers_optifarm_co_uk/EZp8SWE2S-VCn...

[PhilipTreacy]

Hi @DJsummers 

Can you please share some data or your PBIX file so I can see what you're working with.

Regards

Phil

[DJsummers]

Hi @PhilipTreacy , I am linked to an sql server so not sure you would be able to see anything if I shared the pbix file? Also, I'm guessing that is related to what I am seeing...?

[PhilipTreacy · Accepted solution]

Hi @DJsummers 

Use SUMX instead of SUM

New Target = IF('CumulativeWeightsView2'[ParentAge]<35, SUMX('CumulativeWeightsView2', 'CumulativeWeightsView2'[StandardWeight]*0.95))

Regards

Phil

[DJsummers]

@PhilipTreacy Thanks very much for the response.

I have done that (and have learnt about SUMX) but now the new target is enormous, for example, I'd expect to see 0.305 for Day 10. The lowest the new target goes, when changing it to Min is 27353.963

Thanks Again
Dan