Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
So I want to calculate my total sales, but I only want to do it for products that have more than one entry in my database.
I tried to define my measure as
MS_cond = IF(COUNT('products'[name]) > 1,
SUM('products'[items_per_day]),
"")
but it always goes into the ELSE part for every product, even though
items_count = COUNT('products'[name])
on its own returns plenty of products with more than one entry. What am I doing wrong?
Solved! Go to Solution.
Can you give this a try? It should build the list of product with > 1 count and return the amount for just those.
MS_Cond =
VAR _ProductsWithCount =
ADDCOLUMNS (
VALUES ( 'products'[name] ),
"@Count", CALCULATE ( COUNT ( 'products'[name] ) )
)
VAR _MultiProducts =
FILTER ( _ProductsWithCount, [@Count] > 1 )
RETURN
CALCULATE ( SUM ( 'products'[items_per_day] ), _MultiProducts )
Can you give this a try? It should build the list of product with > 1 count and return the amount for just those.
MS_Cond =
VAR _ProductsWithCount =
ADDCOLUMNS (
VALUES ( 'products'[name] ),
"@Count", CALCULATE ( COUNT ( 'products'[name] ) )
)
VAR _MultiProducts =
FILTER ( _ProductsWithCount, [@Count] > 1 )
RETURN
CALCULATE ( SUM ( 'products'[items_per_day] ), _MultiProducts )
This works beautifully, thank you! I would never have thought of that by myself!
User | Count |
---|---|
97 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
96 | |
95 | |
64 | |
57 |