# Which histogram formula is correct?

#### macfuller

##### Active Member
Poking around on the web I find various formulas for creating histograms. I'm getting different results for each formula and I want to know which one is accurate.

I have a table of purchase orders and I want to get the distribution of how many POs are in various dollar ranges (e.g. how many are for between \$100 and \$500). In the Orders table each purchase order can have multiple lines, which add up to the total value of the purchase order. So I need to sum [Extended Merch Amt] for all the lines with the same Orders[PO No.] value to get the full PO value. My base measures are:
Code:
``PO Spend:=SUM ( Orders[Extended Merch Amt] )``
Code:
``PO Count:=DISTINCTCOUNT ( Orders[PO No.] )``
The competing histogram measures are:
Code:
``````PO Count Distribution:=CALCULATE (    [PO Count],
FILTER (
Orders,
AND (
[PO Spend] >= MIN ( tblDollarRanges[Min] ),
[PO Spend] < MAX ( tblDollarRanges[Max] )
)
)
)``````
Code:
``````PO Distribution Count:=CALCULATE (    [PO Count],
FILTER (
VALUES ( Orders[PO No.] ),
COUNTROWS (
FILTER (
tblDollarRanges,
[PO Spend] >= tblDollarRanges[Min]
&& [PO Spend] < tblDollarRanges[Max]
)
)
)
)``````
And my results are:
 Label PO Count Distribution PO Distribution Count Up to \$10 89,426 7,557 \$10 to \$50 133,536 34,782 \$50 to \$100 99,604 32,989 \$100 to \$500 144,745 112,692 \$500 to \$1,000 50,103 50,861 \$1,000 to \$5,000 55,332 65,079 \$5,000 to \$10,000 8,851 12,483 \$10,000 to \$50,000 6,533 9,261 \$50,000 to \$100,000 943 1,100 \$100,000 to \$500,000 828 1,021 \$500,000 to \$1 million 107 125 \$1 million to \$5 million 116 121 \$5 million to \$10 million 22 22 \$10 million to \$100 million 9 9 \$100 million + 1 1 Grand Total 328,103 328,103

<tbody>
</tbody>

The rows for PO Count Distribution add up to more than the Grand Total so I suspect the PO Distribution Count is the accurate one since I'm doing the VALUES(Orders[PO No.]) but I like the simplicity of PO Count Distribution and am wondering if there's a way to make it work better. Any insight as to why they work as they do, and if there's a simpler solution? Thanks.

Last edited:

#### Matt Allington

##### MrExcel MVP
The first one is counting the line level items, not the aggregation of all lines in a PO. Have you tried taking the first one, and replace “orders” with “values(orders[PO no.])”

1,081,657
Messages
5,360,291
Members
400,580
Latest member
Drexl88

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...