Show Maximum for each dimension

[hoernst]

Dear all

I try to create a table in SAC with an uploaded Excel-File. The structure is like

Country -- Customer -- Turnover

I would like to show the customer with the maximum turnover for each country. The result should be a table with multiple countries but for each country only one customer (the one with the maximum turnover in this specific country).
I tried a lot with custom aggragations or setting Top N but nothing worked.

Thanks for your help and best regards

Stefan

[former_member184471 · Accepted solution]

Hi hoernst,

If a chart is sufficient then a new rank option released in 2021.11 will work. It allows to rank for a single dimension if more than ones are used in the chart.

Table does not support this option. If you need a table and use acquired or Live HANA data, then you can use some nested calculation and constant selection. Note BW Live does support constant selection.

My Source data:
Region = your country
Sales Manager = your customer
Sales Revenue = your turnover

First I calculate the Max Value for the sales managers using aggregation function.

The problem now is that as soon as you use the sales manager in the same table, it will aggregated for that sales manager. This is where constant selection comes in handy as it allows to ignore the breakdown or filters on a dimension. As it is part of the restricted measure you have to select a dimension for restriction and select all members, which basically means no restriction and then select Sales Manager (in your case customer) for constant select.

Because constant selection ignores the sales managers in the table, the value displayed is the same max value for each sales manager within each region. What you can do now is compare whether the actual sales revenue matched the max constant selection sales revenue, if so this is your max Sales Manager.

This calculation now returns the sales revenue for the max sales manager and null for everyone else.

Below a table with all the above mentioned calculation, so you can see how the steps evolved.

Now if you create a table only with the last calculation, then you get one Sales Manager per region (expect 2 Sales Manager have the exact same Sales Revenue, then you would see 2)

Please tell me calculations are fun.

Hope that helped,

Christina

[hoernst · Accepted solution]

Hi Christina

thank you very much for your help! This solution worked fine for me. Thanks for the detailed information and step by step guidance.

Best regards
Stefan

[thayumanavan · Accepted solution]

Hi @hoernst

[thayumanavan · Accepted solution]

Hi @hoernst is there any option to do this in live connection