Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Percentage Growth Statement

Hi Experts/Geniuses!
 

Any help would be greatly appreciated!

 
Basically, if you see the data attached it will look like this screenshot. The table on the left shows the percentage growth in the result table. Unfortunately, the calculation isn't correct when you compare it to the right table ((actual/approved target)-1). 

 Hoping to get the script right for the result?  If you see the result by ID, for some reason it's not calculating the individual numerator over denominator values. It's just giving it a standard decimal by what seems ID aggregation? The script logic seems ok to me...

I have a feeling maybe it's all going wrong with the [actual], [forecast] and [approved target] measures I created using: Actual =
CALCULATE (
SUM ( 'Table'[value] ),
ALLEXCEPT('Table', 'Table'[ID], 'Table'[Month],'Table'[Miscellaneous],'Table'[Not Applicable]),
'Table'[Cost] = "Actual"
).
 
Otherwise, I'm thinking maybe the blanks and zero's are also causing confusion in the calculation or that it's possibly not filtering by month granularity for [value] and [actual]. I tried this adjustment to the script but the result is worse-off with results coming back for targets and actuals.
Result =
VAR _Cost =
SELECTEDVALUE ( 'Table'[Cost] )
VAR _act = [Actual]
VAR _forc = [Forecast]
VAR _targ = [Approved Target]
VAR _att =
IF (
ISBLANK ( _act ),
IF ( _Cost = "Forcast" || NOT(ISBLANK(_forc)) || NOT(ISBLANK(_targ)) || NOT(_forc = 0) || NOT(_targ = 0), DIVIDE ( _forc, _targ ) - 1 ),
IF ( _Cost = "Actual" || NOT(ISBLANK(_act)) || NOT(ISBLANK(_targ)) || NOT(_act = 0) || NOT(_targ = 0), DIVIDE ( _act, _targ ) - 1 )
)
RETURN
_att
Thanks in advance for all your suggestions and assistance!
 
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Thanks so much for your help Felix! Much appreciated!! I solved it using this: SUMX(VALUES('Table'[field]), CALCULATE(DISTINCTCOUNT('Table'[field])))

4 Replies

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    Hi Anonymous ,

     

    Looking at your data and the explanation you are providing the only questions is the blank values correct? What is the value you want to have instead of -1?

     

    I have change the result formula in order to give 0

     

     

    Result = 
VAR _Cost =
    SELECTEDVALUE ( 'Table'[Cost] )
VAR _act = [Actual]
VAR _forc = [Forecast]
VAR _targ = [Approved Target]
VAR _att = 
    IF (
        ISBLANK ( _act ),
        IF ( _Cost = "Forecast", DIVIDE ( _forc, _targ, 1 ) - 1 ),
        IF ( _Cost = "Actual", DIVIDE ( _act, _targ, 1 ) - 1 )
    )
RETURN
    _att

     

     

     

    You can set a alternative value on the divide when you have errors, in this case I added the 1 since you are subctrating 1.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Big thanks so much MFelix !! Super helpful! What I realised is that I think the formula is aggregating by ID and all the actual for the numerator and all the target values for denominators which is why it's providing a standard decimal for all the values in a certain time period. 
      When really I would like it to be filtered for each individual ID, year and month (so just each individual row). Do any ideas of appropriate filtering come to mind for this?   Really appreciate all your support!! 

      • MFelix's avatar
        MFelix
        Super User
        5 years ago

        Hi Anonymous 

         

        If I'm getting the model correctly, you don't have values for all the forecast lines that will make it difficult to get the calculation has you need.

         

        I have changed your information to a matrix and has you can see you don't have always values for the forecast.

         

         

        In your formula you are checking if actuals is blank and the first calculation you are making is the division by the forecast.

         

        Can you please tell me in words what is the calculation you want to do? believe this is an issue with the formula itself maybe you need to start no by the actuals but by the forecast or the target.