Search code examples
excelpowerpivotdax

DAX code to count number of orders with n total units

I've got a list of order lines (first table of the screenshot attached). I'd like to know how many orders have 1 unit, how many 2 units, etc.

I usually do this with 2 pivot tables (as shown in the screenshot attached). The problem is that I have to create an intermediate table and if I have more than 1million orders I couldn't have this approach due to excel's number of rows limit.

I've tried using CALCULATE in several ways, but I can't find the correct way to do it...

Here is the screenshot: Tables

Thanks in advance!

Jorge

EDIT: I use MS Excel 2017, not Power BI

Solution

  • The correct way to do it is using the function SUMMARIZE (one nested in the other), like this:

    SUMMARIZE(
    SUMMARIZE(
        Orders, 
        Orders[OrderNo],
        "Sum of Units", SUM ( Orders[Units] ) 
    ),
    [Sum of Units],
    "Number of Orders", COUNTROWS (
        SUMMARIZE(
            Orders,
            Orders[OrderNo],
            "Sum of Units", SUM ( Orders[Units] )
        )
    )
)
ORDER BY [Sum of Units] ASC