Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi!
I want to calculate the average number of SKUs (items) sold per store.
In my data hundreds of stores with thousands of products with sales.
I have success with calculating the number using this measure, but it is - very - slow.
Is there anyway to speed up this measure or structure it in another manner?
AVG SKUs Sold per Store =
var _tab = SUMMARIZE(Sales,
Sales[Store Number],
"SKU",
CALCULATE(
distinctcount('Item'[Item Number]),
FILTER('Item','Item'[Category] <> "Internal" && 'Item'[Category] <> "Unknown" &&
'Item'[Subcategory] <> "Recycle" && [Qty] > 0)
)
)
return
AVERAGEX(_tab,[sku])
Thanks!
Solved! Go to Solution.
Hi @Kr1v3L1337
I'm thinking a "related distinct count" style measure would work well here.
Here's one measure that might work, based on the pattern from the DAX Patterns page above.
I've assumed that [Qty] > 0 would only be true if Items actually appear in the Sales table.
AVG SKUs Sold per Store =
CALCULATE (
AVERAGEX (
VALUES ( Sales[Store Number] ),
CALCULATE (
VAR ItemsFromSales =
SUMMARIZE ( Sales, 'Item'[Item Number] )
RETURN
SUMX ( ItemsFromSales, 1 )
)
),
KEEPFILTERS ( NOT 'Item'[Category] IN { "Internal", "Unknown" } ),
KEEPFILTERS ( 'Item'[Subcategory] <> "Recycle" )
)
Does this measure return correct results, and does it perform any better than your original measure?
Regards,
Owen
Hi @Kr1v3L1337
I'm thinking a "related distinct count" style measure would work well here.
Here's one measure that might work, based on the pattern from the DAX Patterns page above.
I've assumed that [Qty] > 0 would only be true if Items actually appear in the Sales table.
AVG SKUs Sold per Store =
CALCULATE (
AVERAGEX (
VALUES ( Sales[Store Number] ),
CALCULATE (
VAR ItemsFromSales =
SUMMARIZE ( Sales, 'Item'[Item Number] )
RETURN
SUMX ( ItemsFromSales, 1 )
)
),
KEEPFILTERS ( NOT 'Item'[Category] IN { "Internal", "Unknown" } ),
KEEPFILTERS ( 'Item'[Subcategory] <> "Recycle" )
)
Does this measure return correct results, and does it perform any better than your original measure?
Regards,
Owen
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |