Find the number/percentage of orders with 1 item ordered

[gunasai]

Hi,

Consider I have the following table,

Order Number	Item Code	Customer	Qty_ordered	Price
1111	1	ABC	10	100
1111	2	ABC	20	200
2222	1	XYZ	5	50
3333	2	EFG	5	50
4444	2	FFF	10	100

I want to find the Number of orders/Percentage of orders that has ordered only 1 item.

In the above case, it should display only the rows 3,4,5 as "3 orders" or "75% of orders" have only 1 item ordered.

Can you please help me with the DAX or any other method?

Thanks in advance.

[v-chenwuz-msft · Accepted solution]

Hi @gunasai ,

If you want to display the nmuber of orders with 1 item orderd, follow me step by step.

1 create a table visual and drag field Order Number into the table

2 drag the item code into the table visual but set the field’s to count like the following show via the downward-pointing arrow:

3 Then set the filters on the visual, count of Item Code is 1, and click Apply filter, as the image show. you can remove the field count of item code in table if you do not want to display the count of the order number, it won’t affect the existing result.

If you want to display the percentage of 1 order

Try this measure:

Measure =
VAR _allNmuber =
    DISTINCTCOUNT ( 'Table'[Order Number] )
VAR _1order =
    COUNTROWS (
        FILTER (
            SUMMARIZE (
                'Table',
                'Table'[Order Number],
                "if1", DISTINCTCOUNT ( 'Table'[Item Code] )
            ),
            [if1] = 1
        )
    )
RETURN
    DIVIDE ( _1order, _allNmuber )

Here is my pbix file you can reference.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@gunasai , Try a measure like

divide(countx(filter(summarize(Table, Table[Order Number],"_1", distinctCOUNT(Table[Item Code])), [_1]=1),[Order Number]),distinctCOUNT(Table[Order Number]))