Subtotals in PowerBI

[NicolaiHolst]

Hi all,

I have below 3 measures on a calendar dimension in PowerBI:

- No. of Markets Served = DISTINCTCOUNT(Sales[Business Area])

- Total Markets Served = calculate(DISTINCTCOUNT(Sales[Business Area]),'Time',Sources,'Posting Types',ALL(Sales))

- Product Cummunality = [No. of Markets Served]/[Total Markets Served]

Hence, the Product Cummunality is a measure of how many markets (Business Area) a product is sold in vs. Total numbers of markets. Which provides me with below results:

Adult footwear is current at 36,36% - though I would like that number to represent an average of rows (marked above). Currently the ratio at 36,36% is too high, since this is calculated on totals of Adult Footwear. No product have a cummunality at 36,36%.

I'm not able to make a measure like Average([Product Cummunality]) - So any suggestions on how to solve this case (Measure Tabel??)

My data-model looks a below:

Thanks.

[NicolaiHolst]

Found a solution.

Product Cummunality1 = AVERAGEX(VALUES( Sales[Option] ),([No. of Markets Served]/[Total Markets Served]))

Thanks

[amitchandak]

@NicolaiHolst , it may be because of distinct count

- No. of Markets Served = Countx(Summarize(sales, Sales[Foodtware], Sales[product], Sales[Business Area]),[Business Area])

- Total Markets Served = calculate(Countx(Summarize(sales, Sales[Foodtware], Sales[product], Sales[Business Area]),[Business Area]) ,'Time',Sources,'Posting Types',ALL(Sales))

- Product Cummunality = [No. of Markets Served]/[Total Markets Served]