Skip to Content
Author's profile photo Koen Hesters

How to get max (dimension) // Aggregated table

Hi,

I’m seeing a lot of messages where people ask the question to get the max dimension, so they can aggregate the table.

Example:

database

Country Category Material Quantity
France A MatD 35
France A MatE 25
Germany A MatA 80
Italy B MatC 75
Sweden C MatE 10

desired output

Country Category Material Quantity
France A MatD 60
Germany A MatA 80
Italy B MatC 75
Sweden C MatE 10

Quantity is set as sum and for material we display the Material with the highest quantity.

If you use: a variable set as measure and formula: max([material], you will get the wrong result, because he is not looking at the quantity (webi is not that smart).  this results in MatE.

The solution:

  • Create variable (measure): max =Max([Quantity] ForEach([Material];[Category];[Country]))

With this formula we get the max value over Material

0000Capture.PNG

  • Create variable (measure): mat_max =Max([Material] Where([Quantity]=[max] ForAll([Material])))

With this formula we retrieve the Material with the max value regardless “material”, so for us it means we retrieve MatE because the max over material for france is 35

0000Capture.PNG

So we see he filters out the Materialgroup in mat_max, only the material with max value is selected, otherwise is set to blank.

If we set the formula ([Material] Where([Quantity]=[max] ForAll([Material]))) to max, it will show only the material that is not blank, check out document:

Aggregated Dimension

  • Result:

0000Capture.PNG

Check Double Check

0000Capture.PNG

0000Capture.PNG

Assigned Tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Great solution!!

      Thanks a lot ~!

       

      :DDDD

      Author's profile photo Dev Studio77
      Dev Studio77

      Hello Koen, I have to do something similar but in my case Material and Quantity comes from a secondary source data.

      I have alredy merged the the Country dimension but it doesn't work.

      Do you have an idea how to fix it?

      Thanks in advance