cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lajos Frequent Visitor
Frequent Visitor

percent of grand total

Hello Experts,

 

I have a table with sales info.

CountyProductSell-Out
County 1Product 11
County 1Product 22
County 1Product 33
County 2Product 15
County 2Product 215
County 2Product 350

 

Every products have a market share ( % of grand totall).

If I put the slicer by County columns, and select the County1, I want to reach that: in one columns show the Market Share % ( initial ), in the other columns show  the County Share % ( total Product1 in County1 / Grand total County1,  total Product2 in County1 / Grand total County1 ... ).

 

Untitled.png

 

How to solve this situation in the Power BI?

 

Thank you wery much.

 

Best regards,

Lajos.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
bhpage Regular Visitor
Regular Visitor

Re: percent of grand total

Hi,

 

I think I've got what you're looking for. I used that same exact little data set you provided. Here are the two calculations I used:

 

Overall Market Share =
SUMX(FILTER(ALL(Sheet1), SELECTEDVALUE(Sheet1[Product]) = Sheet1[Product]), Sheet1[Sell-Out])
/
SUMX(ALL(Sheet1),Sheet1[Sell-Out])
 
County Market Share = SUM(Sheet1[Sell-Out]) / SUMX(FILTER(ALL(Sheet1), SELECTEDVALUE(Sheet1[County]) = Sheet1[County]), Sheet1[Sell-Out])
 
Here's the output, which matches what you provided as expected output below:
 
% of Grand Total.PNG
 
Let me know if this works for you.
 
Thanks,
Ben
3 REPLIES 3
Highlighted
bhpage Regular Visitor
Regular Visitor

Re: percent of grand total

Hi,

 

I think I've got what you're looking for. I used that same exact little data set you provided. Here are the two calculations I used:

 

Overall Market Share =
SUMX(FILTER(ALL(Sheet1), SELECTEDVALUE(Sheet1[Product]) = Sheet1[Product]), Sheet1[Sell-Out])
/
SUMX(ALL(Sheet1),Sheet1[Sell-Out])
 
County Market Share = SUM(Sheet1[Sell-Out]) / SUMX(FILTER(ALL(Sheet1), SELECTEDVALUE(Sheet1[County]) = Sheet1[County]), Sheet1[Sell-Out])
 
Here's the output, which matches what you provided as expected output below:
 
% of Grand Total.PNG
 
Let me know if this works for you.
 
Thanks,
Ben
Lajos Frequent Visitor
Frequent Visitor

Re: percent of grand total

Dear Ben,

First of all thank you very much for your quick and professional help.

Working perfectly.

 

I just want to ask you, how to set the Overall Market Share format in percentage, because I couldn't set from Field Value settings.

 

Best regards,

Lajos.

bhpage Regular Visitor
Regular Visitor

Re: percent of grand total

If you select the field you want to format in the "Fields" section, and then go up to the "Modeling" tab, you'll see an option to format:

 

% of Grand Total 3.png