Matrix measure between two tables common value includes multi Slicers

[DavePowerBI]

Hi all

I am a newbie and I need some help with a division calculating on the same country between two tables which includes multi values selected via slicers

I have two tables which have a relationship based on Country (I have only included the MarchCountry Total table below as the other table has loads of data security reasons).

I have created one Matrix which consist from the table March Birthday - Country, Store number  and Sale count with some slicers based on certain values (please see Matrix picture below).

I need a measure which looks at each countries Sum of sale count in the matrix and divides with the same country Sale Country Total from the table MarchCountry Total and shows as a %. The same is required for the totals.

Example Austria

29/91 = 23.1%

Example of total for Matrix

2573/6232 = 41.29%

I hope the above is not confusing, appreciate your assistance.

 

 

 

MarchCountry Total Table

Country	Sale Country Total
Algeria	85
Argentina	213
Australia	373
Austria	91
Czech Republic	350
Germany	1804
Denmark	90
Ecuador	73
El Salvador	52
Estonia	5
Hong Kong	3
Hungary	57
India	1860
Indonesia	214
Ireland	47
Israel	12
Italy	185
Japan	492
Jordan	26
Kazakhstan	122
Kenya	53
Kuwait	12
Latvia	5
Puerto Rico	1
Qatar	4
Korea	20
Moldova	2
Romania	127
Russia	620
Total	6232

[Rudy_D]

Hi DavePowerBI,

Please try thease measure :

% = DIVIDE(SUM(March Retail[Sale Count]), SELECTEDVALUE(March Country Total[Sale Country Total]))

Then you format the measure in %.

Regard,

Rudy

[DavePowerBI]

@Rudy_D - thank you, For your measure I am getting Infinity result? See below 

 

[TomasAndersson · Accepted solution]

Hi!
It sounds like you should get your result using just a few measures. I'm not exactly sure how your March Retail table is structured so you might have to adjust accordingly, but something like this.

Sum of Sale Count := 
SUM('March Birthday'[Sale Count])

Sum of Sale Country Total := 
SUM('MarchCountry Total'[Sale Country Total])

Sale Count divided by Sale Country Total :=
DIVIDE(Sum of Sale Count, Sum of Sale Country Total)

 
This is using three separate measures. You could do everything in a single measure, but it's usually considered good practice to create separate measures for basic measures such as "Sum of..." since they often can be reused in several other measures.

Hope this helps!