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:
With this formula we get the max value over 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
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:
Check Double Check
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
6 | |
5 | |
5 | |
5 | |
5 | |
4 | |
4 | |
4 | |
3 | |
3 |