Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I'm stuck on this summarization problem:
I've a calculation with some conditions based on a Sales Table and I just want to aggregate by Customer:
VAR selGoal = 5000
VAR deltaYtd =
IF ( [Act YTD] > selGoal, ( [Act YTD] - selGoal ) )
VAR deltaMon =
CALCULATE ( [Act] - deltaYtd )
VAR lvl0Calc =
CALCULATE (
IF ( deltaYtd > 0, IF ( deltaMon < 0, 0, deltaMon ), [Act] ),
FILTER ( 'Dim Customer', HASONEVALUE ( 'Dim Customer'[CUSTOMER_ID] ) )
)
VAR result =
SUMX (
SUMMARIZE ( 'Dim Customer', 'Dim Customer'[Customer Desc], "value", lvl0Calc ),
[value]
)
RETURN
result
The above calculation should works at customer's maximum level of detail (customer_id) and aggregated by "customer Desc"
The join between Sales and customer is by CUSTOMER_ID
I don't know how to perform the aggregation, for example Nutrilab 2.0 Srl should be 6784.
Any hint is appreciated,
Marco
Solved! Go to Solution.
@ribaldo ,
First create a measure without this Say M1
SUMX (
SUMMARIZE ( 'Dim Customer', 'Dim Customer'[Customer Desc], "value", lvl0Calc ),
[value]
)
Then create a new measure and use above calculation using the measure(M1) you created without that and try
create the new measure for summarize the table with sumx
@Anonymous I created the M2 measure that summarize M1, but it doesn't work:
Thank you for your answer.
I created a measure M1 :
M1 =
VAR selGoal = 5000
VAR deltaYtd =
IF ( [Act YTD] > selGoal, ( [Act YTD] - selGoal ) )
VAR deltaMon =
CALCULATE ( [Act] - deltaYtd )
VAR lvl0Calc =
CALCULATE (
IF ( deltaYtd > 0, IF ( deltaMon < 0, 0, deltaMon ), [Act] ),
FILTER ( 'Dim Customer', HASONEVALUE ( 'Dim Customer'[CUSTOMER_ID] ) )
)
RETURN
lvl0Calc
and then a M2 using M1:
M2 =
SUMX (
SUMMARIZE ( 'Dim Customer', 'Dim Customer'[Customer Desc], "value", [M1] ),
[value]
)
But with no success:
I did something wrong?
@ribaldo ,
First create a measure without this Say M1
SUMX (
SUMMARIZE ( 'Dim Customer', 'Dim Customer'[Customer Desc], "value", lvl0Calc ),
[value]
)
Then create a new measure and use above calculation using the measure(M1) you created without that and try
User | Count |
---|---|
57 | |
21 | |
19 | |
17 | |
16 |
User | Count |
---|---|
85 | |
80 | |
52 | |
37 | |
22 |