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

Thomas Kreis
Thomas Kreis Employee
Third Anniversary First Answer 5 Likes First Comment
edited March 28 in How-To Guides

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:

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.

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.


  • Valentina Sarti
    Valentina Sarti Employee
    Second Anniversary 5 Likes Photogenic Name Dropper
    edited March 22

    Hi Thomas,

    very interesting insight! I've tried to replicate it in one of my applications and withening the block format in the end is a clever workaround! Nice tip, thanks


  • Federica Antonelli
    Federica Antonelli Employee, Knowledge Author
    Third Anniversary 25 Likes 10 Comments First Answer

    Nice tip @Thomas Kreis - I suggest to move this content to the How-to Guide section. @Miran Saric

  • Holly Rieke
    Holly Rieke Administrator, Employee, Knowledge Author, Idea Exchange Manager
    25 Likes 10 Comments Name Dropper First Answer

    @Federica Antonelli Great idea to move this post to the How-to Guide section! I will take care of that.