Histogram formula when bin selection is based on cell value rather than measure

macfuller

Active Member
Joined
Apr 30, 2014
Messages
317
Office Version
  1. 365
Platform
  1. Windows
I'm OK with using measures to assign which bin of a histogram an item should go but I'm having an awful time figuring out how to do it when the column value itself is what determines a bin.

Assume there is a Histogram table with 3 columns of bin label, max val, and min val.

My data table has a column of Days on Hand (DoH) for each item in a storeroom. If an item has a DoH of 12 then I want it to go into bin label 12 of the histogram.

Code:
Days on Hand:=CALCULATE (
    [Storeroom Item Total],
    FILTER (
        VALUES ( 'Storeroom'[Unique Item]),
        COUNTROWS (
            FILTER (
                Histogram,
                Storeroom[DoH] >= Histogram[MinVal]
                    && Storeroom[DoH] < Histogram[MaxVal]
            )
        )
    )
)

But of course the code fails because the Storeroom[DoH] field I'm bumping against the histogram bin levels is not SUM or AVERAGE etc. I've tried COUNTROWS(VALUES(DoH)), FIRSTNONBLANK(VALUES(DoH)) and other constructs, but the first one simply groups the DoH by how many different values of DoH I have. (e.g. if my Storeroom has an item with DoH of 4 at the box quantity, 7 at the roll, and 11 at the case then it places the item in the "3" bin because there are 3 separate DoH values. I'd like one value in the 4 bin, one in 7, and one in the 11 bin.)

It's probably a simple formula but I'm not getting it!
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
I could of course just do a simple pivot table with DoH values as a row and a COUNTROWS() measure, but then I wouldn't have the zero values for DoH values that aren't represented...
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top