1Total count/sum with greater than condition
We have requirement to get total Purchase order count & sum of Purchase order amount for only suppliers greater than 2 purchase orders.
Can you please help achieve total count along with having condition.
Sample Input data:-
| Category | Supplier Name | PO Number | PO Amount |
| Food | SUP1 | PO1 | $100 |
| Service | SUP2 | PO2 | $200 |
| Food | SUP2 | PO3 | $100 |
| Merchandise | SUP3 | PO4 | $600 |
| Service | SUP4 | PO5 | $50 |
| Food | SUP1 | PO6 | $100 |
| Food | SUP1 | PO7 | $100 |
| Service | SUP2 | PO8 | $200 |
| Food | SUP2 | PO9 | $100 |
| Merchandise | SUP5 | PO10 | $600 |
| Service | SUP1 | PO11 | $50 |
| Food | SUP5 | PO12 | $100 |
sample output:-
| Category | Total Suppliers | Total PO's | Total PO Amount |
| Food | 3 | 6 | $600 |
| Service | 2 | 3 | $450 |
| Merchandise | 1 | 1 | $600 |
-
Hi Mehraj,
Can you try using Aggregate Filter to filter Purchase Order count greater than 2?
Screen shot below shows how to apply this filter.
-
Hi Anurag,
Thanks for prompt response. I tried aggregate filter but it does not affect the result shown. As the po# is a string with transaction number. In the screenshot you shared did it show data correctly?
Thanks
Mehraj
-
Hi Mehraj,
It's ok if PO # is a string with transaction number.
it should work. In your screen shot i don't see filter getting applied. No value is selected from list where you see 1, 2, 10, 12.
-
Anurag Malik
Apologies may be screenshot was not showing the value but it is selected. Attaching word file with screenshots and a short recording to compare with & without aggregate filter results.
-
-
Hi Mehraj,
My bad. This will not work. In dimension you have Sourcing_Group and aggregate filter is getting applied for dim Sourcing_Group.
If supplier was used as dimension then this would have worked.
Let me share a different solution for this.
-
-
Hi Anurag,
Thank you so much for detailed video. Solution works perfectly for our requirement. Really appreciate showing how to add dynamic variable.
Have a great day!
Thanks
Mehraj
