Value Filters on Orders with Multiple Lines

[MichaelHearst13]

Hi all,

First post so bear with me.

I have a long list of approximately 80K distinct order numbers. If an order has X quantity of Item 1 and X quantity of item 2, there would be two seperate lines (and so forth). Below:

Order	Line	Item	Quantity
1	1	Apple	2
1	2	Orange	2
2	1	Apple	1
2	2	Orange	1
2	3	Grape	5
3	1	Orange	1

What I am ultimately trying to accomplish is to calculate two buckets, 1) the number of orders with quantities of 5 or less, and 2) the number of orders with greater than 5.

I've tried many different calculations, none of which work. As an example:

Small Orders = CALCULATE(DISTINCTCOUNT('2017_Orders'[Order #]),'2017_Orders'[qty_invoiced]<=5)

Large Orders = CALCULATE(DISTINCTCOUNT('2017_Orders'[Order #]),'2017_Orders'[qty_invoiced]>5)

So in the example above I'd want to show (on two cards): Small Orders 2 and Large Orders 1

Any help or guidance if this has already been addressed in another thread would be greatly appreciated.

Thanks!

[MFelix · Accepted solution]

Hi @MichaelHearst13,

Use the following measures:

Large Orders =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            '2017_Orders',
            '2017_Orders'[Order],
            "Qtd", SUM ( '2017_Orders'[Quantity] )
        ),
        [Qtd] > 5
    )
)

Small Orders =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            '2017_Orders',
            '2017_Orders'[Order],
            "Qtd", SUM ( '2017_Orders'[Quantity] )
        ),
        [Qtd] <= 5
    )
)

Regards,

MFelix

[MichaelHearst13]

Thanks for the clarification! Your solution worked great.

Best regards,

Michael