cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dapperscavenger
Helper IV
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:
 
LocationABC IndicatorFiscal Year+Month+NameL8: ProductSum Min Breach
CountryAC2019-P08-FebProductA12
CountryAC2019-P09-MarProductA2
CountryAC2019-P10-AprProductA0
CountryAC2019-P11-MayProductA0
CountryAC2019-P12-JunProductA10
CountryAC2020-P01-JulProductA24
CountryAC2020-P02-AugProductA8
CountryAC2020-P03-SepProductA30
CountryAC2020-P04-OctProductA30
CountryAC2020-P05-NovProductA17
CountryAC2020-P06-DecProductA19
CountryAC2020-P07-JanProductA0
CountryAC2020-P08-FebProductA17
CountryAC2020-P09-MarProductA23
 
 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:
 
LocationAgreed Min
CountryA5
CountryB5
CountryC8
CountryD5
 
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. 
 
LocationABC IndicatorFiscal Year+Month+NameL8: ProductSum Min BreachMin Breach Adj.
CountryAC2019-P08-FebProductA12-74
CountryAC2019-P09-MarProductA2-84
CountryAC2019-P10-AprProductA0-86
CountryAC2019-P11-MayProductA0-86
CountryAC2019-P12-JunProductA10-76
CountryAC2020-P01-JulProductA24-62
CountryAC2020-P02-AugProductA8-78
CountryAC2020-P03-SepProductA30-56
CountryAC2020-P04-OctProductA30-56
CountryAC2020-P05-NovProductA17-69
CountryAC2020-P06-DecProductA19-67
CountryAC2020-P07-JanProductA0-86
CountryAC2020-P08-FebProductA17-69
CountryAC2020-P09-MarProductA23-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

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

View solution in original post

3 REPLIES 3
dapperscavenger
Helper IV
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
 

Which is good to go!

 

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

 

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

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors