cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

Fair Share Calculation

Hi all.

I am trying to create a series of calculations using Dax, and I am stuck on a piece.

Context: First, I need to calc market share - how much is our account selling of a specific product within the entire geography. Then I would compare that to how much the total account sells in their geopgrahy (aka Fair Share). Then I calculate a gap between those two to see if the account is selling less or more than their share of the product.

Pieces:

``````Market Share =
var _numerator = CALCULATE(sum(Account[Dollars]))
var _denominator = [Total Market Dollars]
return DIVIDE(_numerator, _denominator)``````

Current "Fair Share" formula:

``FairShare = CALCULATE([Market Share],ClientList[Client]="Total Departments")``

Result:

The formula won't go down. Now, in this table, there is a duplication of the products listed below

I've also tried this version of the formula (the table is filtered using the Representation column):

``FairShare = CALCULATE([Market Share],ALL(ClientList[Client]),ClientList[Client]="Total Departments",REMOVEFILTERS(Representation[Representation]))``

Any ideas?? Thank you!

7 REPLIES 7
Community Support

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

Super User

Hi,

Does this measure work?

``FairShare = CALCULATE([Market Share],all(ClientList[Client]))``

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

Try the following formula:

``````Measure =
SUMX(
FILTER(
ALLSELECTED(ClientList[Client]),
ClientList[Client] = "Total Departments"
),
[Market Share]
)``````

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helper I

Thank you! Alas, that formula have me the exact same result (14.8%) as before and as before, only for the top row...

Community Support

Could you please share the relationship between the tables in the Model view?

In your Fair Share formula I see that you have restrictions on client

ClientList[Client]="Total Departments",

so the calculation result you want is consistent (14.8%)?

Best Regards,
Winniz

Helper I

Sure - here is the model. TA and xAOC are the made data sources; the rest are mapping files.

My products are Total Departments (ie the total retailer), then Clients (ie manufacturers). The clients don't total up to the Total Departments; and Total Departments is a singular product pulled from our system. So, it will change as retailer and time period changes. But - I don't want it to change depending the product (row) displayed...

Community Support

If I understand correctly, the Client column in your matrix is the column of the Representation table. So you need to modify the measure to

``FairShare = CALCULATE([Market Share], Representation[Client] = "Total Departments")``

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors