topic Re: Calculate average sku sold per month in DAX Commands and Tips

Calculate average sku sold per month

RVBIBS — Tue, 03 Jan 2023 14:55:43 GMT

I have tried to search for other topics regarding this problem, but can't seem to get any of them to work.

I want to find the average amount of units sold per month per SKU in my DATA table.

This is my table

And this is some of the data

Do anyone has a smart way to do this?

Re: Calculate average sku sold per month

johnt75 — Tue, 03 Jan 2023 15:15:17 GMT

It is safest to build a star schema rather than one flat table. You want to add a proper date table, marked as a date table, and you should also have a separate table for your SKUs, which you could create like

SKU Table = SUMMARIZE('Table', 'Table'[SKU], 'Table'[Item name])

Link both the SKU table and the date table in one-to-many relationships to your fact table, and always use columns from these tables on your visuals and in filters and slicers.

You can then create a measure to show the average quantity like

Avg Quantity = AVERAGE('Table'[Quantity])

You can put this measure into visuals with columns from your SKU and date tables and it will show you the averages broken down by whatever you choose.

Re: Calculate average sku sold per month

FreemanZ — Tue, 03 Jan 2023 15:47:20 GMT

hi RVBIBS

try to

1) add column with this:

YYMM = FORMAT([Date], "YYYYMM")

2) write a measure like this:

AvgByMonthBySKU =

VAR _table =

SUMMARIZE(

data,

data[YYMM],

data[SKU]

)

RETURN

DIVIDE(

SUM(data[Sales]),

COUNTROWS(_table)

)

i tried to verify with a simplified sample, it worked like this: