Forum Discussion
Highlighting based on user selection in histogram
Dear Power BI formus,
This is my first post, and will be a very complex one. I want to know if there is any way for a histogram based on a dynamic distribution can have one bucket highlited based on user selection in a slicer?
I will elaborate below:
I work at a large company with many stores, each with many sales people. I have picked up working on a complex report that was created by someone some time ago that allows one to choose either a store, or a salesperson and see their performance on various metrics (total sales, average revenue per sale, average sale time, etc...) compared to a customizable compare group by percentile.
The report contains a single select slicer for store, employee, metric and multiselect for region, store type, employee type, sale type and more.
The flow of the report is as follows:
End user can select a metric they would like to investigate, then a store and / or an employee to focus on. They can then customize their comparison group, selecting store type, region, and various other slicers. Basically this allows one to compare performance of one store to other stores in the same region, or a different region, or an employee with 10000 sales vs one with only 100 if that makes sense.
The percentiles are not calculated using DAX inbuilt percentile function, rather using a simple percentile formula of (n-i)/(n-1) where n is the number of observations in the sample and i is the rank of the observation of interest (in this case either a store or an employee). Calculating these percentiles is simple enough with measures, but they must be measures and not tables due to the dynamic nature of the percentiles and that they would change depending on the comparission group.
The report contains a histogram showing the distribution of the comparisson group. I am not entirely sure how this histogram works. The person who built this used a toggle measure with selectedvalue and selectedcolumn to have the histogram change based on if someone wants to see the distribution for employees or stores with a slicer. The y axis of the histogram comes from a measure which calculates a table, maintains some slicers with allexcept, and adds a column to that table based on some criteria, and returns a count of that table, so a scalar. The distributions are calculated in measures as well, and referenced through that original metric slicer which links to a parameter field, allowing the x axis of the histogram to change depending on user selection.
My issue lies in that it is a scalar that determines the counts for this distribution, and that contains no information about the stores or the employees within the distribution. This means I haven't found a way to conditionally format this bar chart to highlight with selected value from the employee or store slicer.
Is there a way to do this, or a better way to make the histogram where it is still dynamic based on selections which would make the conditional highlighting easier?
Hopefully there is someone out there willing to read through this and help me out.
Happy Holidays!
1 Reply
- DAX_merchant
Helper II
Edit,
I realize I did not clearly state what I actually want to do with the conditional formatting. If the end user selects a store, and it is in the 10th percentile, I want the 10th percentile column in the histogram to be a different color.