Calculated Column in calculated table not working

[dapperscavenger]

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!

[dapperscavenger]

 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

[dapperscavenger]

Which is good to go!

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

[v-gizhi-msft · Accepted solution]

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