Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Lwpro
Frequent Visitor

Troubleshooting Competitor Share Calculation in Power BI Dashboard

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,

1 ACCEPTED SOLUTION
v-linyulu-msft
Community Support
Community Support

 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:

vlinyulumsft_0-1716368781433.png

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:

vlinyulumsft_1-1716368799264.png

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.

vlinyulumsft_2-1716368821042.png

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:

vlinyulumsft_3-1716368884816.png

 

vlinyulumsft_4-1716368894127.png

vlinyulumsft_5-1716368901236.png

vlinyulumsft_6-1716368922112.png

Here is the related documentation:

ALLSELECTED function (DAX) - DAX | Microsoft Learn

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

vlinyulumsft_7-1716368941604.png

 

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.

 

View solution in original post

4 REPLIES 4
v-linyulu-msft
Community Support
Community Support

 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:

vlinyulumsft_0-1716368781433.png

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:

vlinyulumsft_1-1716368799264.png

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.

vlinyulumsft_2-1716368821042.png

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:

vlinyulumsft_3-1716368884816.png

 

vlinyulumsft_4-1716368894127.png

vlinyulumsft_5-1716368901236.png

vlinyulumsft_6-1716368922112.png

Here is the related documentation:

ALLSELECTED function (DAX) - DAX | Microsoft Learn

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

vlinyulumsft_7-1716368941604.png

 

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
Super User
Super User

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






some_bih
Super User
Super User

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






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.

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors