Dynamic bins for price distribution

Hi Timo1980 ,

 

es, you can absolutely create dynamic price bins that adjust based on user selections in a slicer. This is a common and powerful technique in Power BI that involves using DAX measures in combination with a disconnected "helper" table to dynamically group your data for a histogram.

First, you need to create a disconnected table that will provide the foundation for your chart's x-axis. This table is not related to your other data tables in the model. In Power BI Desktop, navigate to the Modeling tab, select New Table, and enter the following DAX expression. This creates a table named Bin Axis with a single column Value that will act as a multiplier for our bin size.

Bin Axis = GENERATESERIES(0, 1000, 1)

Next, create a measure that will determine the bin size based on the current slicer selection. Right-click your main data table (e.g., 'SalesData'), select New measure, and use a SWITCH statement to define the logic. This measure checks the selected product and returns the corresponding bin width.

Bin Size =
SWITCH(
    TRUE(),
    SELECTEDVALUE('SalesData'[Product]) = 10001, 0.5,
    SELECTEDVALUE('SalesData'[Product]) = 10002, 5,
    1 -- Default bin size
)

Now, create the primary measure that performs the dynamic grouping. This measure calculates the start and end of each bin based on the Bin Size measure and the Value from the Bin Axis table. It then counts the distinct customers whose price falls within that calculated range for each bar on the chart.

Customer Count in Bin =
VAR BinStart = SELECTEDVALUE('Bin Axis'[Value]) * [Bin Size]
VAR BinEnd = BinStart + [Bin Size]
RETURN
    CALCULATE(
        DISTINCTCOUNT('SalesData'[Customer]),
        FILTER(
            'SalesData',
            'SalesData'[Price] >= BinStart && 'SalesData'[Price] < BinEnd
        )
    )

With these DAX components created, you can build your visual. Add a Column chart to your report. Drag the Value column from your Bin Axis table to the X-axis field and your [Customer Count in Bin] measure to the Y-axis field. Add slicers for Product and Country from your main data table. To keep the chart clean, select the visual, go to the Filters pane, add [Customer Count in Bin] as a filter, and set it to "is not blank". The chart will now automatically adjust the binning based on your slicer selections.

To improve the user experience, you can add dynamic labels that appear in the tooltip on hover. Create one final measure that formats the bin's start and end values into a text string. Then, drag this new measure into the Tooltips field of your visual's properties.

Bin Label =
VAR BinStart = SELECTEDVALUE('Bin Axis'[Value]) * [Bin Size]
VAR BinEnd = BinStart + [Bin Size]
RETURN
    IF(
        [Customer Count in Bin] > 0,
        FORMAT(BinStart, "0.00") & " - " & FORMAT(BinEnd, "0.00")
    )