topic Re: Average basket size containing SKU's in DAX Commands and Tips

Average basket size containing SKU's

Rpxx — Thu, 08 Dec 2022 17:34:39 GMT

I have a table with ordered SKU's. One line is a one position on one order. In one order client can buy many different SKU's. I'd like to calculate and analize average basket size (value) included next SKU's. Could you help me?

For example for SKU 01111 it will be (order 001) SUM=6; (order 003) SUM=16, so avg order is (16+6)/2=11

Date	\| No of order	\| SKU	\| Value
2022-12-01	\| 001	\| 02222	\| 2
2022-12-01	\| 001	\| 01111	\| 4
2022-12-01	\| 002	\| 02222	\| 10
2022-12-01	\| 003	\| 02222	\| 3
2022-12-01	\| 003	\| 04444	\| 2
2022-12-01	\| 003	\| 06666	\| 9
2022-12-01	\| 003	\| 01111	\| 2
2022-12-02	\| 004	\| 02222	\| 4
2022-12-02	\| 005	\| 03333	\| 2
2022-12-02	\| 005	\| 04444	\| 7
2022-12-02	\| 005	\| 01111	\| 4
2022-12-02	\| 006	\| 05555	\| 7

SKU	\| Avg basket size with SKU
011111	\| 11
022222	\| ?

Re: Average basket size containing SKU's

Anonymous — Fri, 09 Dec 2022 01:34:33 GMT

Hi Rpxx ,

Here are the steps you can follow：

1. Create measure.

Avg basket size with SKU = var _column=SELECTCOLUMNS(FILTER(ALL('Table'),'Table'[Date]=MAX('Table'[Date])&&'Table'[SKU]=MAX('Table'[SKU])),"1",[No of order]) var _count= CALCULATE(DISTINCTCOUNT('Table'[No of order]),FILTER(ALL('Table'), 'Table'[SKU]=MAX('Table'[SKU])&&'Table'[Date]=MAX('Table'[Date]))) var _sum= SUMX(FILTER(ALL('Table'),'Table'[No of order] in _column&&'Table'[Date]=MAX('Table'[Date])),[Value]) return DIVIDE(_sum,_count)

2. Result:

Best Regards,

Liu Yang

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

Re: Average basket size containing SKU's

Rpxx — Mon, 19 Dec 2022 09:55:04 GMT

Could you describe what is the next steps to calculate these numbers in your file? I don't understand...

Re: Average basket size containing SKU's

Rpxx — Mon, 19 Dec 2022 11:33:49 GMT

This is my expected result. Could you help me? Date is not important...