Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
PatriciaHare123
Frequent Visitor

Filtering by histogram bins in Power BI

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

10-20%0.20
220-40%0.40.2
340-60%0.60.4
460-80%0.80.6
580-100%1.010.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.
 
4 REPLIES 4
lbendlin
Super User
Super User

 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

 

lbendlin_0-1729119551704.png

 

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

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...

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):
PatriciaHare123_2-1729093557536.png

I hope it makes sense.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Top Solution Authors