Which histogram formula is correct?

macfuller

Active Member
Joined
Apr 30, 2014
Messages
271
Office Version
365
Platform
Windows
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:
LabelPO Count DistributionPO Distribution Count
Up to $1089,4267,557
$10 to $50133,53634,782
$50 to $10099,60432,989
$100 to $500144,745112,692
$500 to $1,00050,10350,861
$1,000 to $5,00055,33265,079
$5,000 to $10,0008,85112,483
$10,000 to $50,0006,5339,261
$50,000 to $100,0009431,100
$100,000 to $500,0008281,021
$500,000 to $1 million107125
$1 million to $5 million116121
$5 million to $10 million2222
$10 million to $100 million99
$100 million +11
Grand Total328,103328,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:

Some videos you may like

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)

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,221
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.])”
 

Watch MrExcel Video

Forum statistics

Threads
1,101,949
Messages
5,483,840
Members
407,415
Latest member
Anton1999

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top