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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.