SAC Table Threshold based on one KPI but display on another?

[vitran23]

Does anyone know if there is the ability now or in the future where you can set the threshold based on one KPI but display the output on another KPI? Example is within a BEX query where you can set the exception to be calculated on column B but display the coloring on column A?

[DebjitSingha · Accepted solution]

Hi vitran23,

It is possible even today.

You can define SAC threshold on measure A based on condition from measure B. Here is an example-

Say I want to set threshold on MaxRevenue

System lets you select other measure. In this case system lets you select "Revenue" for condition:

Measure A and B should have some relation in order to use this setup. In above example we can use percentage only. If you do not want to use percentage as a deciding factor then you can create calculated measure instead and just use them for threshold setup.

Beside you can perform other tricks as well. Here is an example:

How to show the Tick and cross marks in conditional formatting?

Viewers/ readers : if you find shared information helpful, feel free to vote (voting arrows on left side) for it.

Thanks,

Debjit

[vitran23 · Accepted solution]

Ok the issue I have with my data source is I have multiple dimensions that give me actual and target. My measures aren't defined as measure 1 actual, measure 2 budget which is making it impossible to do the comparison as you have shown.

I have 1 measure but 2 dimensions to split out the actual to budget. Hope that makes sense.

Forgot to add to my original post this is for formatting how the table object looks with the data.

[DebjitSingha · Accepted solution]

Having examples included in original post is always helpful. Otherwise we may go into spiral of miscommunication, like your last post 🙂 BTW did you checked the last reply on you previous post?

You need to harmonize this either at model level or at the story table level first.

To be honest if your DS is BW then, harmonizing report output at BW level is a better option.

Thanks,

Debjit

[former_member751964 · Accepted solution]

Hi vitran23,

It looks like the original question was answered correctly. Please, if something drastically changes in your question, then it might be better to mark the current question as solved and open another one with different initial data.

Thank you!

[vitran23 · Accepted solution]

Ok for the case you explained I would need to create 2 measures, one for say MTD and another for Target in order to do the threshold as you've explained.

What if I have a BW query with a dual structure where my time structure contains a MTD and Target selections and the Key Figures are in the rows.

I've attached a example below of how the data is represented in my BW query.

Row 1, Column B & C are imported into SAC as a Time Structure.

Column A, Rows 2-3 is my KF structure.

With the way the threshold works I'm not seeing a way to be able to use the same measure (KPI 1) and compare it's MTD to Target. Is there a trick to possibly accomplish this or is the only solution to create KPI 1 MTD, KPI 1 Target, KPI 2 MTD, KPI 2 Target, and etc... in order to have them as measures usable in thresholds?

[DebjitSingha · Accepted solution]

Hi vitran23,

As mentioned in my previous post, you need to consolidated KPI and measure combination. Either at the source or in SAC story level. You need to create calculated measure. With condition you can end up with only two calculated measure representing the entire setup. Once you have two measure, you should be able to implement the threshold as I explained.

Thanks,

Debjit

[vitran23 · Accepted solution]

Why your stating is that a 2 structure query won’t work with creating thresholds in this manner?

I would need the dedicate KPI 1 MTD and KPI 1 Budget and etc…

[DebjitSingha · Accepted solution]

issue is not with two structure, rather the placement of measure values. Create calculated measure and consolidate before using them with threshold. By now you may have guessed the limitation of threshold and why it will not work with yoir current setup.

[vitran23 · Accepted solution]

I think I’m lost on when you say consolidate. Is there a good example of consolidating with a calculated measure?

[DebjitSingha · Accepted solution]

i think the question you should ask yourself can you change or simplify days structure. To be honest your current data structure will pose challenge with data representation with any existing BI tool. Gentlemen below suggested the same things. Create calculated measure to change data representation. Assuming you are aware “how to created calculated measures”, once you have is simplified you should be able to follow steps shared earlier.

again the question you posted vs what you are asking are two different things. Now your query is more towards how to simplify your data structure, whereas you started with how to use threshold based on two kpi structure. 🙂

I think moderator noticed this and notified in this thread.

[zee_siddique15 · Accepted solution]

Hi @DebjitSingha, may I please know what you mean by

[zee_siddique15 · Accepted solution]

Hi @DebjitSingha, may I please know what you mean by "Measure A and B should have some relation in order to use this setup. In above example we can use percentage only. If you do not want to use percentage as a deciding factor then you can create calculated measure instead and just use them for threshold setup.

[VijayetaSharma]

Hello,

You need to change your measure representation into a two column measure format, before you can take advantage of threshold function. Seems like above two responses pointing the same thing.

thanks,

Vijayeta

[vitran23]

Ok that’s what I was thinking was going to need to happen.

So there is no way for me to keep a single measure row separated by 2 dimension columns and use the threshold.

I would need to have dedicated KPI 1 MTD and KPI 1 Target measures.

[VijayetaSharma]

Not sure if you noticed, that's what other two responses are pointing towards 🙂

[vitran23]

thanks for pointing it out. I realize what the suggestion was now.

[mayuri_ghosh]

We can set the threshold based on one KPI but display the output on another KPI.

[saikhancs35]

Hi,

When i am trying to use above option, the measure option is coming disabled to me. Any idea, as what could be the reason for that? I am using BW4HANA Live connection.

Below is my requirement:

I have a table showing Period Accuracy and Target. I want to highlight period accuracy based on target value.

So if [Target]-[Period Accuracy] >2 then green color, if [Target]-[Period Accuracy] = 2 then red color, if [Target]-[Period Accuracy] > 2 then red.

Please ignore the numbers as this is dev data.

[N1kh1l]

saifkhancs35

Threshold based on another measure is not supported on live connection. Its supported on imported connection model only.

There is an influence request for the same which can be upvoted

Improvement Request Details - Customer Influence (sap.com)

Nikhil

[former_member743805]

Hi Vi Tran,

it could work with a new calculated KPI:

If(MTD-Target < 0 ; KPI1 - 1)

If(MTD-Target > 0; KPI1 +1)

The syntax is not correct, I just wanted to clarify what I mean. This way your calculated KPI is whether higher or lower as the KPI 1 where you want to display the threshold, while the threshold basically depends on the deviation between the other KPIs MTD and Target.

Kind regards,

Sascha