Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
RVBIBS
Regular Visitor

Calculate average sku sold per month

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

RVBIBS_0-1672757531681.png

And this is some of the data

RVBIBS_1-1672757582314.png

 

Do anyone has a smart way to do this?

2 REPLIES 2
FreemanZ
Super User
Super User

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:
FreemanZ_1-1672760789049.png
FreemanZ_0-1672760767039.png

 

johnt75
Super User
Super User

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.