Best Practice Question

Hi VulcanPromance ,

To what I can understand here you issue is based on the calculation of the percentage of Yearly costs per country this can be achieve by making the following measures:

Monthly Price = 
VAR temp_Table =
    SUMMARIZE (
        'Price Table';
        'Price Table'[Bundle Name];
        'Price Table'[Price];
        "Users"; DISTINCTCOUNT ( Licenses[UserID] )
    )
RETURN
    SUMX ( temp_Table; 'Price Table'[Price] * [Users] )

Yearly Price = [Monthly Price] * 12

I'm assuming the Yearly is just multiplyig by 12 months.

Then you need to calculate the percentage of costs per country selected:

% per Country = [Yearly Price] / CALCULATE([Yearly Price];all(Employees[Country]))

If you have a country table that should be based on that table and not employees table.

Now just make the measures for the Shared Costs:

Shared_Costs = SUM('Cost Table'[Cost]) // Just used as a auxiliary calculation

Shared_Costs_Selected_Countries = [% per Country] * [Shared_Costs]

Markup 5% = [Shared_Costs_Selected_Countries] * 0,05

Overhead 10% = [Shared_Costs_Selected_Countries] * 0,1

Fee 3% = [Shared_Costs_Selected_Countries] * 0,03

BO4 Reporting 12% = [Shared_Costs_Selected_Countries] * 0,12

Total Shared Costs = [Shared_Costs_Selected_Countries]+Measure_Table[Markup 5%]+[Overhead 10%]+[Fee 3%]+[BO4 Reporting 12%]

This values will give you the calculation for the shared costs now you only need to sum the value of the last measure with the Yearly costs and you are all set:

Total Costs Per country = [Yearly Price] + [Total Shared Costs]

Check the image below and the PBIX file attach.

Any questions please tell me.

Hi VulcanPromance ,

Add the following measure to your calculation:

Count of users =
CALCULATE (
    MAXX (
        SUMMARIZE (
            'Price Table';
            'Price Table'[Bundle Name];
            'Price Table'[License Name];
            "@User_Count"; DISTINCTCOUNT ( Licenses[UserID] )
        );
        [@User_Count]
    );
    ALLEXCEPT ( 'Price Table'; 'Price Table'[Bundle Name] )
)

Now just need to redo the Montlhy price and everything else will calculate accordingly:

Monthly Price = 
VAR temp_Table =
    SUMMARIZE (
        'Price Table';
        'Price Table'[Bundle Name];
        'Price Table'[Price];
        "Users"; [Count of users]
    )
RETURN
    SUMX ( temp_Table;'Price Table'[Price] * [Users])

Just added the Count of Users in your measure. There is a small difference but believe is rounding.

Check result attach.