on 07-31-2022 10:15 PM
How can I create a custom formula in a sac story where, if the % variance between plan and actual is greater than 999% then the formula should give "nm"?
I have tried to create this variance not as a calculated measure and the format of formula is not supported by SAP.
One would think that the SAP would do any calculation what is done in excel. These are very basic formula and are helptul for management reporting.
Anyone has any idea how to do this?
kocomandi
Currently the IF condition in the calculated measure does not accept string and numeric as parameter at the same time. for e.g. IF(Variance%>0.5,"abc","def") will not work. But this will work IF(Variance%>0.5,1,0). This is as per design as all numeric calculations are measures and any categorical calculationlike "high" , "low" etc based on measure is a calculated dimension.You can try a workaround as below
This can be done as a calculated measure in the stories. You will need two restricted measure namely Actual and Plan and then a calculated measure for variance %. You can create the final categorical analysis as calculated dimension to see if the variance % exceeds a certain % to call it "nm". Example shown below.
Actual Restricted Measure
Plan Restricted Measure
Variance % Calculated Measure
Finally you will need to create a Calculated dimension in the form of measure based dimension and then lay out the condition of variances. In my example I have done variance greater than 5%. See below.
Final Output
Please upvote/accept if this helps you.
Regards
Nikhil
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Actually I resolved this by adding a column after all my $ variance and there I build an multiple if conditions and it worked.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Hikhil,
Thank you for your response. However, I want this the nw not in a separate column, but on the same column as % variance.
Yes, my report includes already the restricted measures for actual and plan.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
86 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.