Forum Discussion
Matrix measure between two tables common value includes multi Slicers
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 |
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!
3 Replies
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!
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
