Divide values from different tables

[ChristophEmrich]

Hi all,

I am trying to calculate the market share of certain products in a specfic country, in a specific year based on filtered values. For that I have two tables: Table_Sales and Table_Market and two slicers: Year and Country.

In the Table_Sales I have the list of products and their sales volumes in each country and each year:

Year	Country	Product	Sales
2018	France	Chocolate	240
2018	France	Strawberry	60
2018	France	Vanilla	168
2018	Germany	Chocolate	100
2018	Germany	Strawberry	20
2018	Germany	Vanilla	170
2018	Italy	Chocolate	48
2018	Italy	Strawberry	32
2018	Italy	Vanilla	56
2019	France	Chocolate	60
2019	France	Strawberry	108
2019	France	Vanilla	168
2019	Germany	Chocolate	12
2019	Germany	Strawberry	180
2019	Germany	Vanilla	48
2019	Italy	Chocolate	120
2019	Italy	Strawberry	60
2019	Italy	Vanilla	160
2020	France	Chocolate	320
2020	France	Strawberry	144
2020	France	Vanilla	48
2020	Germany	Chocolate	150
2020	Germany	Strawberry	135
2020	Germany	Vanilla	300
2020	Italy	Chocolate	32
2020	Italy	Strawberry	56
2020	Italy	Vanilla	152

In the Table_Market, I have the total market volume each year in each country:

Year	Country	Market Volume
2018	Germany	1,000
2018	Italy	800
2018	France	1,200
2019	Germany	1,200
2019	Italy	1,000
2019	France	1,200
2020	Germany	1,500
2020	Italy	800
2020	France	1,600

Now I somehow need to connect the tables and create a measure. I have a relationship on the key YEAR_COUNTRY but other than that, I don't know how to do it.

What I'd like to see is if in the slicers the selected values are "Select all", of course I'd like to see the market share of all products comined in all years and in all markets. If a year is selected, it should only be that year's market share.

I've tried a lot of stuff already, with SUMX, CALCULATE and others but nothing seems to work.

Hope I could make myself clear. Would appreciate any help! Thank you.

Christoph

[Anonymous · Accepted solution]

Hi,

I think you need two calculated tables and three measures.

Table 1: Calendar

Calendar =
DISTINCT ( UNION ( ALL ( Table_Market[Year] ), ALL ( Table_Sales[Year] ) ) )

Table 2: Countries

Countries =
DISTINCT (
    UNION ( ALL ( Table_Market[Country] ), ALL ( Table_Sales[Country] ) )
)

Relationships:

Countries[Country] -> Table_Market[Country]

Countries[Country] -> Table_Sales[Country]

Calendar[Year] -> Table_Market[Year]

Calendar[Year] -> Table_Sales[Year]

All relationships are one to many with cross-filtering as single.

Measure 1: MarketSize

MarketSize = SUMX(Table_Market,Table_Market[Market Volume])

Measure 2: SalesVolume

SalesVolume = SUMX(Table_Sales,Table_Sales[Sales])

Measure 3: MarketShare %

Market Share = DIVIDE(Table_Sales[SalesVolume],Table_Market[MarketSize],0)

For the slicers, use the Calendar[Year] and Countries[Country] fields.

MarketSize, SalesVolume, and MarketShare% will be correctly displayed.

[ChristophEmrich]

Thank you, that did the trick!