CODEWITHSUNDEEP
excelpowerpivotdax
jormaga
jormaga

Reputation: 311

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

Upvotes: 0

Views: 829

Answers (2)

jormaga
jormaga

Reputation: 311

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

Upvotes: 1

Foxan Ng
Foxan Ng

Reputation: 7151

This solution involves using the SUMMARIZECOLUMNS function, which seems to be only available in Power BI, Analysis Services 2016, or Excel 2016.

SUMMARIZECOLUMNS is a much more efficient way to build summary table.

The two summary tables are rebuilt using the expression as follows in Power BI:

new table

Order Summary = 
SUMMARIZECOLUMNS(
    Orders[OrderNo],
    "Sum of Units", SUM(Orders[Units])
)

-

Unit Summary = 
SUMMARIZECOLUMNS(
    'Order Summary'[Sum of Units],
    "Number of Orders", COUNTROWS('Order Summary')
)

The result will be as expected:

result

Upvotes: 1

Related Questions