Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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.
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.
@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]))
User | Count |
---|---|
97 | |
87 | |
78 | |
74 | |
70 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |