Troubleshooting Competitor Share Calculation in Power BI Dashboard

[Lwpro]

Hello everyone,

I'm facing some issues with measures in Power BI and would like to ask for the community's help in resolving these matters.

Currently, I'm working on a management dashboard that includes cards and charts to visualize the market share of my company and competitors. I have measures defined to calculate both my company's share and the competitors' share, and I'm encountering discrepancies in the results when filtering competitor brands.

Here's a summary of my scenario and the problems I'm facing:

Problem Context:

My management dashboard includes cards for both my company's share and competitors' share, as well as a bar chart to compare the shares.
The measures to calculate my company's share seem to be working correctly, but I'm having issues with the measure to calculate competitors' share. From what I've noticed, the result in the competition measure remains fixed, meaning it calculates the total of the competition, but when I filter by data segmentation for a specific competitor, it returns the overall total of the competition.

Measures Used:

To calculate my company's share, I'm using the following measure:

% My Brand Share =
CALCULATE(
'Measures'[% share],
'Brand'[Brand] = "My Brand"
)

To calculate competitors' share, I'm using the following measure (which is presenting problems):

% Competitors' Share =
CALCULATE(
'Measures'[% share],
'Brand'[BRAND] <> "My Brand"
)

I'm seeking a solution to ensure that the measure correctly responds to selections made in the Power BI interface, regardless of the number of brands selected. Any help or suggestions on how to adjust the measures to resolve these issues would be greatly appreciated. If you need more information or clarification, I'm available to provide it.

Thank you for your attention and assistance!

Best regards,

[v-linyulu-msft · Accepted solution]

 Thanks for the reply from @some_bih , please allow me to provide another insight:
Hi,@Lwpro 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2. I have a question if you can answer me: is "'Measures'[% share]" a measure or a calculated column? Because I can't create a table named "'Measures" in the real test, and in the CALCULATE() function his first position is needed for the expression, the screenshot of the reported error is as follows:

According to your expression, I will consider it as a measure in the following solution.

3.Secondly, if your field is indeed the content of a table, you can try to establish a relationship between the two tables in which case you can correlate the content of the two tables.

4.Finally, you can try the following measure:

% Competitors' Share = 
CALCULATE(
'Measures'[% share],
FILTER(ALLSELECTED(Brand),'Brand'[Brand]<> "My Brand")
)

If you use the ALLSELECTED() function, the ALLSELECTED() function gets the context that represents all the rows and columns in the query, while retaining the context outside of the explicit filters and row and column filters. , the following is a simple example:

Here is the related documentation:

ALLSELECTED function (DAX) - DAX | Microsoft Learn

5.Here's my final result, which I hope meets your requirements.

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Leroy Lu

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

[some_bih]

Hi @Lwpro just based on your description, it is hard to spot issue / propose solutions.

It could be that your measures should be adjusted for some of ALL* functions, like ALLSELECTED and similar.

Please check some of links below for your scenario.

Link1

Link2

[some_bih]

Hi @Lwpro it is not clear how you literally filter data segmentation, so please share file with examples for outputs for given inputs, as your model and relationships "create" filtered output.

[Lwpro]

I have a dashboard related to the share of my brand compared to the competition, that is, the shelf space of my brand compared to the competition's space.

I have a field called "Total space in centimeters," which shows the product's space in centimeters at the time it was recorded. For example, it was noted that my product or the competitor's product in category "X" has 5.5 cm of space. I also have a field called "Total category space in centimeters," which is the total space of the category in general. In other words, each product has a specific space on the shelf, and there is a total space for the product category.

My question is this: I have a bar chart called "Share by Category," which should show the share by category. The problem I'm facing is that the percentage of my measures, which is the sum of "Total space in centimeters" divided by the sum of "Total category space in centimeters," is correct when viewed line by line.

Here is the logic of the Measure:

% Share =
DIVIDE([Sum Space], [Sum Category])

The measures [Sum Space] and [Sum Category] are the SUMX of the fields mentioned above.

The dashboard shows the results correctly when it is not filtered. However, when I apply a filter, for example, by the category "Cookies," the bar chart, instead of showing the result as "100%" (since I am filtering a specific category), shows the percentage compared to other categories.