Calculating a % of a "subtotal"

[AndyBlasiak]

Hi Power BI Community,

Could you advise me on the DAX function or the full syntax to use to calculate % share for a "subtotal?

In the below table I want to calculate for each respective country a % share of product sold in respective country total sales (as shown in the last column).

Country	Product	Sales (tonnes)	Product Structure per Country
USA	Paper	12	13%
USA	Steel	35	37%
USA	Wood	47	50%
UK	Wood	22	39%
UK	Steel	35	61%
Poland	Paper	45	49%
Poland	Steel	12	13%
Poland	Wood	34	37%

Looking forward to hearing from you.

Regards

Andrzej

[Nathaniel_C · Accepted solution]

Hi @AndyBlasiak ,
Please try this:

Measure = 

var _product = MAX('Totals'[Product])
var _sales = MAX('Totals'[Sales (tonnes)])
var _country = max('Totals'[Country])
var _totalSales = SUM('Totals'[Sales (tonnes)])


var _totalCountrySales = CALCULATE(SUM('Totals'[Sales (tonnes)]), Filter(All('Totals'),'Totals'[Country]=_country))
var _divide = divide(_sales,_totalCountrySales)

return _divide

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Nathaniel_C]

Hi @AndyBlasiak 
Bonus measure - if you want to calculate the % of a product that each country  produces with just a small change in the measure try this:

% of Product Sales per Country = 

var _product = MAX('Totals'[Product])
var _sales = MAX('Totals'[Sales (tonnes)])
var _country = MAX('Totals'[Country])

var _totalSales = SUM('Totals'[Sales (tonnes)])


var _totProductSales = CALCULATE(SUM('Totals'[Sales (tonnes)]), Filter(All('Totals'),'Totals'[Product]=_product))
var _divide = divide(_sales, _totProductSales)

return _divide

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel