PowerBI Dynamic binning (ranges change) based on value of measure

Question

I’m trying to represent some continuous data via binning. Continuous weighting data of an area should be binned as: VeryHigh, High, Low, VeryLow. The weighting values are based on an interaction between certain Types of events grouped by an Area and so can change depending on the Type selected by the report user.

I have included some sample data below and an outline of what’s been done so far.

Start with five sets of area data (A-E). Within each is one or more incident Types. Each incident has a Weighting and the number of times (Count) it occurs within the Area.

Add a calculated column CC_ALL_WGT (weighting * count)

Create a measure:

M_WGT = DIVIDE(SUM(sample_data[CC_ALL_WGT]), SUM(sample_data[4_count]))

This makes sense once grouped by Area and we can see that the Area gets an overall Weighting Score

This can be altered by slicing the data based on which Type of incident we wish to inspect:

We can also set up additional measures to get the Min; Max; Median from the Measure based on the Type selection:

M_MIN_M_WGT = IF(
    countrows(values(sample_data[1_area])) = 1,
    sample_data[M_WGT],
    MINX(
        values(sample_data[1_area]),
        sample_data[M_WGT]
    )
)

Which change as expected when a Slicer selection is made

Also set up a measure to determine the Mid-Point between the Minimum and the Median and Mid-Point between the Maximum and the Median

M_MidMinMed =
sample_data[M_MED_M_WGT] - ((sample_data[M_MED_M_WGT] - sample_data[M_MIN_M_WGT]) / 2)

What I would like to do with these values is create a banding based on the following:

VeryLow: (Minimum to MinMed mid-point) Low: (MinMed to Median) High: (Median to MedMax mid-point) VeryHigh: (MedMax to Maximum)

So based on the following selection

The bins would be set up as follows

VeryLow (0.59 to 0.76)
Low (0.76 to 0.93)
High (0.93 to 1.01)
VeryHigh (1.01 to 1.1)

Area A would be in Bin 4 (VeryHigh); Area B in Bin 2 (Low); Area C in Bin 1 (VeryLow); Area D in Bin 2 (Low); Area E in Bin 4 (VeryHigh)

If select specific Types to review (via the slicer) the bins would be set up as follows:

VeryLow (0.35 to 0.61)
Low (0.61 to 0.88)
High (0.88 to 1.06)
VeryHigh (1.06 to 1.24)

So checking M_WGT (with types specified in the slicer):

Area A would be in Bin 4 (VeryHigh); Area B in Bin 2 (Low); Area C in Bin 1 (VeryLow); Area D in Bin 1 (VeryLow); Area E in Bin 4 (High)

NOTE - The change in bin classification for Area D from Low to VeryLow

This is where I get stuck. This post specifies how to apply a static bin range: https://community.powerbi.com/t5/Desktop/Histogram-User-defined-bin-size/m-p/69854#M28961 but I’ve not been able to do this using dynamic or changing values (the Min; Max; Media; Midpoint) depending on selection.

The closest I’ve managed to apply is as follows:

Range =
VAR temp =
    CALCULATE ( sample_data[M_WGT] )
RETURN
    IF (
        temp < 0.76,
        "1_VeryLow",
        IF (
            AND ( temp > 0.76, temp <= 0.93 ),
            "2_Low",
            IF (
                AND ( temp > 0.93, temp <= 1.01 ),
                "3_High",
                "4_VeryHigh"
            )
        )
    )

Which permitted the following:

While I can then associate the Bins with a visual there are a number of things wrong with it. Firstly binning is occurring at the TYPE level not the AREA level. Secondly I’m manually setting the range values.

When I say Type levels what I mean is that they’re being binned at this level:

Whereas what I would like the histogram to be representing are the M_WGT values at the Area level.

If I slice by Area A only the problem is easier to see:

What would I like is for there to be one representation of Area A in the histogram (the bin for 1.10), not the three currently being shown (for each Type 1.9; 1; 0.35)

Hopefully I’ve managed to convey the problem and requirement.

Appreciate any advice or insight.

EDIT: Link to Report + Data source is here: https://www.dropbox.com/sh/oganwruacdzgtzm/AABlggr3-xqdMvPjuR9EyrMaa?dl=0

Can you put the sample data in text form so it's easier to duplicate? A link to a PBIX would be useful too. — Alexis Olson
@AlexisOlson I've edited the original post to include a link to the data (csv) and the report (.pbix) — jayuu

Alexis Olson Alexis Olson · Accepted Answer · 2020-05-06T18:08:32

You can define the bucket for an area all in a single measure:

Bucket = 
VAR Weights =
    SUMMARIZE ( ALLSELECTED ( sample_data ), sample_data[1_area], "Wgt", [M_WGT] )
VAR MinW = MINX ( Weights, [Wgt] )
VAR MaxW = MAXX ( Weights, [Wgt] )
VAR MedW = MEDIANX ( Weights, [Wgt] )
VAR MinMedW = ( MinW + MedW ) / 2
VAR MedMaxW = ( MedW + MaxW ) / 2
VAR CurrW = CALCULATE( [M_WGT], ALLSELECTED( sample_data[2_type] ) )
RETURN
    SWITCH (
        TRUE (),
        CurrW <= MinMedW, "1_VeryLow",
        CurrW <= MedW,    "2_Low",
        CurrW <= MedMaxW, "3_High",
        CurrW <= MaxW,    "4_VeryHigh"
    )

This summarizes the weights over everything within your filter selections (ALLSELECTED) and then defines your boundaries as you specified. Then we calculate the weight for the current area across all selected types and pass that into the switch where we check the values from low to high.

Now you can't use a measure as an axis for a chart so if you want these buckets on the axis, I'd recommend defining an independent table.

Ranges =
DATATABLE (
    "Range", STRING,
    {
        { "1_VeryLow" },
        { "2_Low" },
        { "3_High" },
        { "4_VeryHigh" }
    }
)

Put Ranges[Range] on the axis and define a counting measure as appropriate.

CountArea =
COUNTROWS ( FILTER ( sample_data, [Range] = SELECTEDVALUE ( Ranges[Range] ) ) )

I don't really know what you're trying to count, whether it should be a distinct count, or if 4_count should be involved or not but modify this counting measure as necessary.

PowerBI Dynamic binning (ranges change) based on value of measure

1 Answers