Forum Discussion
Troubleshooting Competitor Share Calculation in Power BI Dashboard
Thanks for the reply from some_bih , please allow me to provide another insight:
Hi,LwproRegarding 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.
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.
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.