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.
Hello All,
I have 2 different products, Product 1 & Product 2. For each product, I have plotted the [Sales] and [Expected], in blue and orange respectively. I have created a new measure that combines the two by taking the [Sales] curve which switches to [Expected] when it begins.
MEASURE =
var _tbl =
ALLSELECTED ( Expected )
var cutoff =
CALCULATE ( MINX ( _tbl, [Date] ) )
return
CALCULATE (
SUM ( Sales[Sales] ),
FILTER ( _calendar, _calendar[Date] < cutoff )
)
+ SUM ( Expected[Sales] )
When I select both products however, they do not sum properly. I believe there is an issue with either my variable 'cutoff' (MINX), or '_tbl' (ALLSELECTED), but I have had no luck fixing it. Could I please get some advice?
Product 1:
Product 2:
Both Selected:
See the dip between the start of the 1st and 2nd [Expected] curves.
I have included the sample file below. Please let me know if you have issues accessing it.
Note: I would like for the measure to apply to each product first, then sum the result. Not sum the curves then apply the measure.
Thank you!
Solved! Go to Solution.
If you define measures [SumSales] = SUM ( Sales[Sales] ) and [ExpectedSales] = SUM ( Expected[Sales] ), then you can write the composite like this:
SUMX (
'Product',
IF (
ISBLANK ( [ExpectedSales] ),
[SumSales],
[ExpectedSales]
)
)
Note: IF.EAGER instead of IF may be slightly more efficient here.
Hi @Palmtop ,
Please try:
Measure2 =
VAR _a =
SELECTCOLUMNS ( 'Product', "Product", [Product] )
VAR _b =
ADDCOLUMNS (
_a,
"cutoff",
MINX (
FILTER ( ALL ( 'Expected' ), [Product] = EARLIER ( [Product] ) ),
[date]
),
"Sales",
CALCULATE (
SUM ( Sales[Sales] ),
FILTER ( 'Sales', [Product] = EARLIER ( [Product] ) )
),
"Expected",
CALCULATE (
SUM ( Expected[Sales] ),
FILTER ( 'Expected', [Product] = EARLIER ( [Product] ) )
)
)
VAR _c =
ADDCOLUMNS (
_b,
"Value", IF ( MAX ( '_calendar'[Date] ) < [cutoff], [Sales], [Expected] )
)
RETURN
SUMX ( _c, [Value] )
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If you define measures [SumSales] = SUM ( Sales[Sales] ) and [ExpectedSales] = SUM ( Expected[Sales] ), then you can write the composite like this:
SUMX (
'Product',
IF (
ISBLANK ( [ExpectedSales] ),
[SumSales],
[ExpectedSales]
)
)
Note: IF.EAGER instead of IF may be slightly more efficient here.
Thank you @AlexisOlson , worked like a charm. I had tried a sumx before trying the posted measure but had not thought to include the IF ( ISBLANK ( ) ) condition.
@v-jianboli-msft Your solution also worked for me as well, thank you.
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 |
---|---|
47 | |
26 | |
19 | |
15 | |
10 |
User | Count |
---|---|
57 | |
50 | |
44 | |
21 | |
19 |