In this blog post we will learn how to conditionally highlight Max values of any measures in a SAC story without using any scripting elements/custom css.
Example Scenario:
We often have this requirement to highlight the maximum (or minimum) of any measures like Revenue , Quantity Sold in a BI Report or Dashboard table. One common approach to perform that analysis is to sort the table or perform a Top N/Bottom N analysis. But sometimes sorting or Top N is not possible when we cannot change the layout of the report or we need to display all the members and not only the Top N.
2. SAC Story: The story before conditional formatting to highlight the max values.
3. SAC story with conditional format: This is what we try to achieve as an exercise in this blog. As there is no standard feature to highlight MAX of any measure dynamically we will use few SAC features to achieve the same.
Steps to achieve the conditional formatting in the story
The threshold should be based on comparison to a measure and not Number Ranges (default option). In this case I will use the Max Sales Const measure as it has the maximum value for the periods for each product. Next I will just use >=100% in the threshold condition so that those values get highlighted which are equal to the max value from the restricted measure.
Finally I will replace the threshold symbol with a highlighted background by changing the the Styling option of the Table widget and change the option from symbol to color background as shown below.
Last step is to to just enable the show Threshold option for the table widget to apply the thresholds.
What we get as a result of above steps is below formatted table in the story with highlighted max values of the measure.
Points to Note:
Useful Links:
Summary: This way we can achieve conditional highlighting of Max values of any measures in a SAC story which enables instant data comparison in a visual effective manner without using any scripting elements
If you think this post has helped you, please like and share your thoughts in the comments section below. Incase of any queries please ask the same in the comments section and I would be happy to answer the same.
Thanks for reading. I hope you enjoyed the blog.
Check the SAP Analytics Cloud topic page for more up to date information https://community.sap.com/topics/cloud-analytics
follow and read other informative blog posts on SAC browse the below link
https://blogs.sap.com/tags/67838200100800006884/
Regards
Nikhil
Example Scenario:
We often have this requirement to highlight the maximum (or minimum) of any measures like Revenue , Quantity Sold in a BI Report or Dashboard table. One common approach to perform that analysis is to sort the table or perform a Top N/Bottom N analysis. But sometimes sorting or Top N is not possible when we cannot change the layout of the report or we need to display all the members and not only the Top N.
- The Model: Below is a snapshot of the model used for this blog. Its a simple analytic model (acquired) based on a flat file.
2. SAC Story: The story before conditional formatting to highlight the max values.
3. SAC story with conditional format: This is what we try to achieve as an exercise in this blog. As there is no standard feature to highlight MAX of any measure dynamically we will use few SAC features to achieve the same.
Steps to achieve the conditional formatting in the story
- Go to the SAC story in edit mode and create an Aggregation Measure to get the MAX of the measure values in the story. In this case Its Order value. I created an aggregation measure called Max Sales.
Please ensure that the operation is MAX, the measure is same that we have in story and Aggregated dimension is the one on which we want the max to be calculated. In our case it is the periods (date dimension) - Next I will create a Restricted Measure on the aggregation Measure created in previous step to get a constant value for all member of the period. In this case I create Max Sales Const as the restricted measure Please note that the constant dimension should be the same on which the max calculation was done in previous step.
- Next I use the threshold feature of SAC story to conditional format the max values. First I will add a threshold to the Account dimension. Please note the calculated measures created in previous steps need not be part of the measures displayed in the story and can be removed from account member selection.
The threshold should be based on comparison to a measure and not Number Ranges (default option). In this case I will use the Max Sales Const measure as it has the maximum value for the periods for each product. Next I will just use >=100% in the threshold condition so that those values get highlighted which are equal to the max value from the restricted measure.
Finally I will replace the threshold symbol with a highlighted background by changing the the Styling option of the Table widget and change the option from symbol to color background as shown below.
Last step is to to just enable the show Threshold option for the table widget to apply the thresholds.
What we get as a result of above steps is below formatted table in the story with highlighted max values of the measure.
Points to Note:
- The above steps were carried out on table widget in a story based on classic acquired model. The same approach should work on stories based on planning model also.
- The above steps can be extended to highlight Minimum values also. The changes will be in the step creating aggregation measure where the operation will change to MIN instead of MAX. Even below and above Average value conditional formatting can be given a try.
Useful Links:
- Aggregation Measure: https://help.sap.com/docs/SAP_ANALYTICS_CLOUD/00f68c2e08b941f081002fd3691d86a7/a64893e144f544bbaeade...
- Restricted Measure:https://help.sap.com/docs/SAP_ANALYTICS_CLOUD/00f68c2e08b941f081002fd3691d86a7/df0e123a79624e68a0735...
Summary: This way we can achieve conditional highlighting of Max values of any measures in a SAC story which enables instant data comparison in a visual effective manner without using any scripting elements
If you think this post has helped you, please like and share your thoughts in the comments section below. Incase of any queries please ask the same in the comments section and I would be happy to answer the same.
Thanks for reading. I hope you enjoyed the blog.
Check the SAP Analytics Cloud topic page for more up to date information https://community.sap.com/topics/cloud-analytics
follow and read other informative blog posts on SAC browse the below link
https://blogs.sap.com/tags/67838200100800006884/
Regards
Nikhil
- SAP Managed Tags:
2 Comments
