Filtering by histogram bins in Power BI

[PatriciaHare123]

Hi Everyone, 

In Power BI I have created a histogram that shows Number of People per Attendance Rate.

To do so I have created additional table that defines the ranges for the histogram, which looks like this:

HistogramBins:
IDBinMaxMin

1	0-20%	0.2	0
2	20-40%	0.4	0.2
3	40-60%	0.6	0.4
4	60-80%	0.8	0.6
5	80-100%	1.01	0.8

Then I created the measure that would count how many people falls into each range by Attendance rate:

Number of people per range =

VAR minbin = SELECTEDVALUE('Histogrambins'[Min])

VAR maxbin = SELECTEDVALUE('Histogrambins'[Max])

VAR segment =

FILTER(

    ADDCOLUMNS(

        SUMMARIZE('People', 'People'[ID]),

        "@Attendance", [% Attendance rate]),

        [@Attendance] < maxbin &&

        [@Attendance] >= minbin)

RETURN CALCULATE([Number of People], segment)

It works nice for the Histogram itself, but I would like to add the option to click on Histogram bins and to have the rest of the report filtered for People[ID] that fall into each range.

In the report I have slicer with Period (which is selected to show last 6 months) and an overview of Attendance rate per month which uses the measure % Attendance rate:

 % Attendance rate = DIVIDE ([Number of People]), [Number of Invitees]).

It works when I am not filtering by histogram bins.

I have tried to use CONCATENATEX list of People ID and then to filter the meausre by CONTAINSSTRING using People ID from the People table that is connected to People ID in the Attendance table, but when I click on specific bin, in the overview by month, it omits the months when Attendance rate falls below selected range per this month.

I would like to separate somehow the inital range of Attendance rate from the second calculation of the Attendance rate so when I, for example, click on range 80-100% I would see the overview for people that fall into this range for selected period, even if for some months in the selected period, it falls below 80%. 

Would appreciate any help or hint.

[lbendlin]

 but I would like to add the option to click on Histogram bins and to have the rest of the report filtered for People[ID] that fall into each range.

There is no data model relationship.  Clicking on a bin is not the same as filtering the bins. Add another measure filter to the people table visual

[PatriciaHare123]

The People table visual is functioning correctly. However, the interaction between the histogram and: 1) the area graph (Attendance by Year Month) and 2) the column chart (People and attendance rate per weekday is not working properly).

The main issue is assigning specific People IDs to the histogram bins and filtering the measures in the area graph and column chart to calculate data only for those IDs. For example, if I select the 40-60% bin and there is one person, both graphs will display data for that person, even if the attendance rate for certain months (area graph) or weekdays (column chart) falls below 40% or above 60%.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[PatriciaHare123]

I have prepared the Sample Data and report here: Sample.pbix.

There are 3 key visuals:

1. histogram,

2. attendance by year month area which works fine when not filtered by histogram,

3. people and attendance rate by weekday column chart which works fine when not filtered by histogram.

Basically, I would like to add the possibility to filter the report by the histogram and:

1. have the area graph per year month to only calculate the Attendance rate over time for people that fall into chosen bin for the selected months (even if in certain months in the selected period it is above/below the chosen range),

2. have the column chart with number of people per weekday calculate correctly number of people (now, when filtered with histogram, the measure calculates correctly the total number of people per filtered months, but shows incorrect split per weekday as per below (total is correct, but per day not):

I hope it makes sense.