How to show the value of different dimensioned cube only in total line

Thomas Kreis
Thomas Kreis Employee
Fourth Anniversary 25 Up Votes Level 100: Foundations of Building in Board Level 200: Building A Planning Solution in Board
edited March 2023 in How-To Guides
1088x612_Article_Cover_How-to-Guide (002).png

Here is something I have implemented at a customer, and I would like to share with you. It may be helpful when facing a similiar use case.

Use case

  • One cube is on a dimension Article and cotains an Order Value
  • The second cube is on Main Class, which is more aggregated than Article and contains a Limit for the Order Value
  • Now I want to show both cubes in a single layout on Article grouped by Main Class and check on Main Class level and on Grand Total level the delta between Order Value and Limit

The main problem is now the Total and Grand Total of the Limit is the sum of the repeated value of the Limit on Main Class level per Article. Additionally, summary options like Average or Max Value do not work, as they are not working in the grand total:

image.png

I created a very simple solution without any additional cubes or dataflows by assuring that the repeated value exists only once per group:

  • I added the Ranking Function Counter next to my Limit Cube.
  • I added an if formula showing only the value, when counter is equal to 1.
  • I changed the color in the block format of the cells to white in order to make them invisible.
image.png

image.png

As a result I get the value of a cube only once in the total line and still totaled up in the Grand Total on top. Even though the cube is dimensioned contrary to the axis. Now I can easily check within one layout how my Order Value is compared to my limit without using a second data view or procedure.

Since there is no Dataview option like “Show value only in total lines”, I hope this is a useful trick for you.

Tagged:

Comments

Categories