Cost per product and country - how can I create that table?

[Anonymous]

I want to create a table like the image above. The costs per country and product are the sum of 3 different cost centers, which are stored individually in Power Bi. For example, the cost of Country A and Product A listed here is $10. However, this €10 consists of three costs €3 + €3 + €4. The goal is to be able to filter the data by year, period (i.e. quarter 1, 2, 3, 4) and also cost section 1-3. I need filters that filter all the data on the page by country, quarter, and cost section, and filters within the visual so that the visual only sees the cost per product of this one country A and product A. My question would now be how to create this. Could someone help me?

[Anonymous · Accepted solution]

Hi @Anonymous ,

I will create a sample to show you more details and give you some advice.

Firstly, your table should look like as below.

Then create dim tables like DimYear/DimPeriod/Dim Cost Section... and so on. Data model should look like as below.

For reference:

Understand star schema and the importance for Power BI

Measure:

Measure = 
VAR _SUM_CountryAndProduct =
    CALCULATE ( SUM ( 'Table'[Sales] ) )
VAR _Count =
    CALCULATE (
        COUNT ( DimProduct[Product] ),
        FILTER ( 'Table', _SUM_CountryAndProduct <> BLANK () )
    )
VAR _Weight_avg_per_country =
    DIVIDE ( _SUM_CountryAndProduct, _Count )
RETURN
    IF (
        HASONEVALUE ( DimProduct[Product] ),
        IF (
            _SUM_CountryAndProduct = BLANK (),
            "Not available",
            _SUM_CountryAndProduct
        ),
        _Weight_avg_per_country
    )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.