cancel
Showing results for
Did you mean:
Frequent Visitor

percent of grand total

Hello Experts,

I have a table with sales info.

 County Product Sell-Out County 1 Product 1 1 County 1 Product 2 2 County 1 Product 3 3 County 2 Product 1 5 County 2 Product 2 15 County 2 Product 3 50

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 ... ).

How to solve this situation in the Power BI?

Thank you wery much.

Best regards,

Lajos.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
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:

Let me know if this works for you.

Thanks,
Ben
3 REPLIES 3
Highlighted
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:

Let me know if this works for you.

Thanks,
Ben
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.

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: