HANA data model: SUM DISTINCT

Dear community,

we are trying to build a graphical data model in HANA for reporting purposes in SAP Lumira.

In this particular scenario, I would need a function which basically sums up a measure (i.e. system cost) based on distinct values of another column (ID) directly in HANA, similar to a count distinct. I need a general solution in the data model, because the calcucation should be transparent to the user of course and should work for all possible dimensions that the user selects in the front end.

For example: I have systems that have costs assigned. Each system is assigned to 1 or multiple regions and these regions are structured in a hierarchy.

When I want to make an analysis over the regions, those system that are linked to multiple regions are counted multiple times of course, but that is not correct in this scenario. The system cost is linked to the system and does not change regardless if the system supports multiple regions.

Region 1   Region 2   System ID   Cost
EU         Austria    1           100
EU         Austria    2           200
EU         Germany    1           100
EU         Germany    3           300
       
SUM Austria:   300     
SUM Germany:   400     
SUM EU:        600    

I have looked at a lot of discussions, but found no suitable answer. A hint in the right direction would be very helpful.

Best regards,

Harald