Histogram with dynamic bins determined by filters

[AlexWinter]

Hi Everyone, 

I am having difficulty in creating a histogram to suit my requiremerts that other discussions around dynamic binning do not seem to be able to solve. 

I have a single table, with some columns that will be used for slicing and a single column measurement that contains decimal values. The values in this column can be on a very large scale, from <1 to 10,000, and using the default binning appoach will group a siginificant number of measurements into the first bar giving a false representation that the data is positively skewed.

Example Data: 

Group	Measurement Value
Group A	0.0001
Group A	0.0001
Group A	0.0001
Group A	0.0002
Group A	0.0002
Group A	0.0003
Group B	10
Group B	20
Group B	20
Group B	20
Group B	20
Group B	30
Group C	10,000
Group C	20,000
Group C	20,000
Group C	30,000
Group C	30,000
Group C	30,000

By default grouping, most of Group A and B would be in the first column count if creating a histogram on all data. Where if filtering in each group would have positive skew, normal, and negative skew.

I'm looking for help in a solution that would, on selection of group slicer and given a configurable value for size of bin (e.g. 10):

- Automatically determine appropriate banding sizes

- Determine count from filtered measurement per bucket
- Plot via Stacked column chart 
 

Whould appreciate any help!
 

[Anonymous · Accepted solution]

Hi @AlexWinter ,
Depending on the information you provided, you can refer to the following steps:
1.Add a measure.

Measure = 
IF (
    SELECTEDVALUE ( 'Table (2)'[Number] )
        >= CALCULATE (
            MIN ( 'Table'[Measurement Value] ),
            FILTER ( 'Table', 'Table'[Group] = "Group A" )
        )
        && SELECTEDVALUE ( 'Table (2)'[Number] )
            <= CALCULATE (
                MAX ( 'Table'[Measurement Value] ),
                FILTER ( 'Table', 'Table'[Group] = "Group A" )
            ),
    "Group A",
    IF (
        SELECTEDVALUE ( 'Table (2)'[Number] )
            >= CALCULATE (
                MIN ( 'Table'[Measurement Value] ),
                FILTER ( 'Table', 'Table'[Group] = "Group B" )
            )
            && SELECTEDVALUE ( 'Table (2)'[Number] )
                <= CALCULATE (
                    MAX ( 'Table'[Measurement Value] ),
                    FILTER ( 'Table', 'Table'[Group] = "Group B" )
                ),
        "Group B",
        "Group C"
    )
)

 Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ZahraaShabana]

Hi v-yifanw-msft, 

Thank you for this answer, how could we know how many groups should be in our data? here's the case I have: 

I'm working on a project in Power BI where I have a dataset with approximately 1000 entries, each identified by a unique ID. Each entry contains various attributes such as model, machine name, etc., along with a set of samples for testing observation.

I'm trying to create histograms for this data, but I'm encountering challenges due to the significant variation in sample distributions across different IDs. The binning requirements vary greatly from one ID to another.

My goal is to have a dropdown list (or slicer) where I can select an ID, and the histogram dynamically adjusts its bins based on the selected ID's data.

Could someone please guide me on how to implement this dynamic binning functionality in Power BI? Any suggestions or best practices for structuring the data to facilitate this process would also be greatly appreciated.

I've attached what the data looks like: