How do you calculate a conditional percent in a matrix?

[Anonymous]

Hello-

I'm very new to Power BI and would appreciate some advice. I have the following data table:

I want to have this output:

How do you write a measure that sums a specific Producer's sales to their own region (Domestic Sales) divided by the total of what that Producer sells to get % Domestic Sales?

TIA!

[Eric_Zhang]

---

@Anonymous wrote:

Hello-

I'm very new to Power BI and would appreciate some advice. I have the following data table:

I want to have this output:

How do you write a measure that sums a specific Producer's sales to their own region (Domestic Sales) divided by the total of what that Producer sells to get % Domestic Sales?

TIA!

---

@Anonymous

Could you post any sample data? Basically I think you can create some measure like.

percentage = divide(sum('Table'[producer sales]),calculate(sum('Table'[domestic sales]), ALLEXCEPT('Table','Table'[region])))

[Anonymous · Accepted solution]

Hi Eric-

Thank you! I ended up creating 3 measures, one for each producer region. For example: 

Europe Domestic % = (SUMX(Filter('Summary Table', 'Summary Table'[Producer]="Europe"),[Europe]) / (SUMX(Filter('Summary Table', 'Summary Table'[Producer]="Europe"),[Total]) ))

But your solution would be better.

Sample data: 

PRODUCER	JAPAN	NORTH AM	EUROPE	KOREA	TAIWAN	CHINA	ROW	Total
Europe	10	8	10	15	10	5	5	63
North America	8	5	5	10	5	15	8	56
Japan	10	10	10	10	10	10	10	70
Korea,Taiwan, China & Row combined	5	10	5	5	10	6	10	51
Europe	10	5	10	10	5	10	6	56
North America	5	5	15	5	15	5	8	58
Japan	5	5	5	5	5	5	5	35
Korea,Taiwan, China & Row combined	5	10	10	10	6	8	10	59
Europe	10	15	5	5	10	10	15	70
North America	15	10	10	15	5	6	12	73
Japan	15	15	15	15	15	15	15	105
Korea,Taiwan, China & Row combined	10	5	5	6	8	8	5	47

[Ashish_Mathur]

Hi,

Given this dataset, what result are you expecting?

[Anonymous]

Desired output from the sample data provided above:

Producer	Domestic Sales	Total Producer Sales	% Domestic
Europe	25	189	13%
North America	20	187	11%
Japan	30	210	14%
Korea,Taiwan, China & Row combined	92	157	59%
Total	167	743	22%

[Ashish_Mathur]

Hi @Anonymous,

You may refer to my solution here.

Hope this helps.

[chethan]

@Anonymous

here you go

& find the attached file here

[Anonymous]

Thank you @chethan for the file. 

I'm looking for the DAX query/ies that would calculate "Domestic Sales %", so some little logic needs to be included into the query given that I have mulitple producers shipping to multiple regions.

The query needs to "find" the total domestic sales to the regions as well as the grand total of the producers sales as illustrated by the sample below.

Domestic Sales for:

Europe = "25" (10+10+5)

Japan "30" (10+5+15)

North America "20" (5+5+10)

Grand Total Producer Sales:

Europe = "189" (63+56+70)

Japan "210" (70+35+105)

North America "187" (56+58+73)

and then calculate Domestic %:

Europe 25  / 189  = 13%

Japan 30  / 210 = 14%

North America 20  / 187  = 11%

At this point I'm going to stick with the 3 measures that I created. I'm sure that there is a more eloquent solution out there but my hack will do for now.

Thank you again for your assistance!