cancel
Showing results for
Did you mean:
Helper IV

## Calculated Column in calculated table not working

Hi all

I've got data that comes out in a daily format, and I've created a table to summarize my key figure (Min Breaches) into a monthly format:

HistoricMonthly = SUMMARIZE('HistoricExtract',[Location],[L8: Product],[ABC Indicator],DimDate[Fiscal Year+Month+Name],"Sum Min Breach",SUM('HistoricExtract'[Min Breaches]))

This gves me the below table:

 Location ABC Indicator Fiscal Year+Month+Name L8: Product Sum Min Breach CountryA C 2019-P08-Feb ProductA 12 CountryA C 2019-P09-Mar ProductA 2 CountryA C 2019-P10-Apr ProductA 0 CountryA C 2019-P11-May ProductA 0 CountryA C 2019-P12-Jun ProductA 10 CountryA C 2020-P01-Jul ProductA 24 CountryA C 2020-P02-Aug ProductA 8 CountryA C 2020-P03-Sep ProductA 30 CountryA C 2020-P04-Oct ProductA 30 CountryA C 2020-P05-Nov ProductA 17 CountryA C 2020-P06-Dec ProductA 19 CountryA C 2020-P07-Jan ProductA 0 CountryA C 2020-P08-Feb ProductA 17 CountryA C 2020-P09-Mar ProductA 23

Per country, there's an acceptable (agreed) no. per month, which I've got stored in another table [Locations] and I created a relationship between the Location columns of the two tables:

 Location Agreed Min CountryA 5 CountryB 5 CountryC 8 CountryD 5

I'm trying to create a column (Min Breach Adj.) which calculates the monthly minus the agreed monthly.

Min Breach Adj. = 'HistoricMonthly'[Sum Min Breach]-SUM(Locations[Agreed Min])

However, this is giving me weird results.  For example, for country A, it should only subtract 5 (e..g 12 - 5 = 7) but instead it has subtracted 86.

 Location ABC Indicator Fiscal Year+Month+Name L8: Product Sum Min Breach Min Breach Adj. CountryA C 2019-P08-Feb ProductA 12 -74 CountryA C 2019-P09-Mar ProductA 2 -84 CountryA C 2019-P10-Apr ProductA 0 -86 CountryA C 2019-P11-May ProductA 0 -86 CountryA C 2019-P12-Jun ProductA 10 -76 CountryA C 2020-P01-Jul ProductA 24 -62 CountryA C 2020-P02-Aug ProductA 8 -78 CountryA C 2020-P03-Sep ProductA 30 -56 CountryA C 2020-P04-Oct ProductA 30 -56 CountryA C 2020-P05-Nov ProductA 17 -69 CountryA C 2020-P06-Dec ProductA 19 -67 CountryA C 2020-P07-Jan ProductA 0 -86 CountryA C 2020-P08-Feb ProductA 17 -69 CountryA C 2020-P09-Mar ProductA 23 -63

Looking to understand what might be causing this.  It might just be my calculation that's off, or maybe there is a better way to adjust the monthly data by the agreed min figure.

(Also, the Adj. figure should not go below 0, but that was going to be the next step)

Thank you!

1 ACCEPTED SOLUTION
Community Support

Hi,

I think you can try this more pretty formula:

``````Min Breach Adj. =
VAR a =
'HistoricMonthly'[Sum Min Breach] - CALCULATE ( SUM ( Locations[Agreed Min] ) )
RETURN
IF ( a < 0, 0, a )``````

Best Regards,

Giotto

3 REPLIES 3
Helper IV

ooookay, so I'm an idiot and think I've just figured it out using:

Min Breach Adj. = 'HistoricMonthly'[Sum Min Breach]-calculate(SUM(Locations[Agreed Min]))

Well, typing it all up helped get my brain working apparently.  So now I just need to get it to not show any negative numbers

Helper IV

Which is good to go!

Min Breach Adj. = MIN('IHistoricMonthly'[Sum Min Breach],MAX(0,'HistoricMonthly'[Sum Min Breach]-calculate(SUM(Locations[Agreed Min]))

Community Support

Hi,

I think you can try this more pretty formula:

``````Min Breach Adj. =
VAR a =
'HistoricMonthly'[Sum Min Breach] - CALCULATE ( SUM ( Locations[Agreed Min] ) )
RETURN
IF ( a < 0, 0, a )``````

Best Regards,

Giotto

Announcements