Forum Discussion

dapperscavenger's avatar
dapperscavenger
Helper V
6 years ago
Solved

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!

Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    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

  • dapperscavenger's avatar
    dapperscavenger
    Helper V
    6 years ago

     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's avatar
      dapperscavenger
      Helper V
      6 years ago

      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's avatar
        v-gizhi-msft
        Community Support
        6 years ago

        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