title: PowerBI Dynamic binning (ranges change) based on value of measure
body: <p>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.</p>
  
  <p>I have included some sample data below and an outline of what’s been done so far.</p>
  
  <p>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.</p>
  
  <p><a href="https://i.stack.imgur.com/GvMTr.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/GvMTr.png" alt="enter image description here"></a> </p>
  
  <p>Add a calculated column CC_ALL_WGT (weighting * count)</p>
  
  <p><a href="https://i.stack.imgur.com/4FYLT.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/4FYLT.png" alt="enter image description here"></a></p>
  
  <p>Create a measure:</p>
  
  <pre><code>M_WGT = DIVIDE(SUM(sample_data[CC_ALL_WGT]), SUM(sample_data[4_count]))
  </code></pre>
  
  <p>This makes sense once grouped by Area and we can see that the Area gets an overall Weighting Score</p>
  
  <p><a href="https://i.stack.imgur.com/ZDqzP.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ZDqzP.png" alt="enter image description here"></a> </p>
  
  <p>This can be altered by slicing the data based on which Type of incident we wish to inspect:</p>
  
  <p><a href="https://i.stack.imgur.com/JMqnl.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/JMqnl.png" alt="enter image description here"></a></p>
  
  <p>We can also set up additional measures to get the Min; Max; Median from the Measure based on the Type selection:</p>
  
  <pre><code>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]
      )
  )
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/XWgBZ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/XWgBZ.png" alt="enter image description here"></a> </p>
  
  <p>Which change as expected when a Slicer selection is made</p>
  
  <p><a href="https://i.stack.imgur.com/N3L8K.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/N3L8K.png" alt="enter image description here"></a></p>
  
  <p>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</p>
  
  <pre><code>M_MidMinMed =
  sample_data[M_MED_M_WGT] - ((sample_data[M_MED_M_WGT] - sample_data[M_MIN_M_WGT]) / 2)
  </code></pre>
  
  <p>What I would like to do with these values is create a banding based on the following:</p>
  
  <p>VeryLow: (Minimum to MinMed mid-point)
  Low: (MinMed to Median)
  High: (Median to MedMax mid-point)
  VeryHigh: (MedMax to Maximum)</p>
  
  <p>So based on the following selection</p>
  
  <p><a href="https://i.stack.imgur.com/roBZB.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/roBZB.png" alt="enter image description here"></a></p>
  
  <p>The bins would be set up as follows</p>
  
  <ol>
  <li>VeryLow (0.59 to 0.76)</li>
  <li>Low (0.76 to 0.93)</li>
  <li>High (0.93 to 1.01)</li>
  <li>VeryHigh (1.01 to 1.1)</li>
  </ol>
  
  <p>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)</p>
  
  <p>If select specific Types to review (via the slicer) the bins would be set up as follows:</p>
  
  <p><a href="https://i.stack.imgur.com/s9Jsv.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/s9Jsv.png" alt="enter image description here"></a></p>
  
  <ol>
  <li>VeryLow (0.35 to 0.61)</li>
  <li>Low (0.61 to 0.88)</li>
  <li>High (0.88 to 1.06)</li>
  <li>VeryHigh (1.06 to 1.24)</li>
  </ol>
  
  <p>So checking M_WGT (with types specified in the slicer):  </p>
  
  <p>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)</p>
  
  <p>NOTE - The change in bin classification for Area D from Low to VeryLow</p>
  
  <p>This is where I get stuck. This post specifies how to apply a static bin range: <a href="https://community.powerbi.com/t5/Desktop/Histogram-User-defined-bin-size/m-p/69854#M28961" rel="nofollow noreferrer">https://community.powerbi.com/t5/Desktop/Histogram-User-defined-bin-size/m-p/69854#M28961</a> but I’ve not been able to do this using dynamic or changing values (the Min; Max; Media; Midpoint) depending on selection.</p>
  
  <p>The closest I’ve managed to apply is as follows:</p>
  
  <pre><code>Range =
  VAR temp =
      CALCULATE ( sample_data[M_WGT] )
  RETURN
      IF (
          temp &lt; 0.76,
          "1_VeryLow",
          IF (
              AND ( temp &gt; 0.76, temp &lt;= 0.93 ),
              "2_Low",
              IF (
                  AND ( temp &gt; 0.93, temp &lt;= 1.01 ),
                  "3_High",
                  "4_VeryHigh"
              )
          )
      )
  </code></pre>
  
  <p>Which permitted the following:</p>
  
  <p><a href="https://i.stack.imgur.com/c5gpW.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/c5gpW.png" alt="enter image description here"></a></p>
  
  <p>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.</p>
  
  <p>When I say Type levels what I mean is that they’re being binned at this level:</p>
  
  <p><a href="https://i.stack.imgur.com/t9pli.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/t9pli.png" alt="enter image description here"></a></p>
  
  <p>Whereas what I would like the histogram to be representing are the M_WGT values at the Area level.</p>
  
  <p>If I slice by Area A only the problem is easier to see:</p>
  
  <p><a href="https://i.stack.imgur.com/WnLNw.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/WnLNw.png" alt="enter image description here"></a></p>
  
  <p>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)</p>
  
  <p>Hopefully I’ve managed to convey the problem and requirement.</p>
  
  <p>Appreciate any advice or insight.</p>
  
  <p><strong>EDIT:</strong>
  Link to Report + Data source is here: <a href="https://www.dropbox.com/sh/oganwruacdzgtzm/AABlggr3-xqdMvPjuR9EyrMaa?dl=0" rel="nofollow noreferrer">https://www.dropbox.com/sh/oganwruacdzgtzm/AABlggr3-xqdMvPjuR9EyrMaa?dl=0</a></p>