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,
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
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 |
---|---|
38 | |
26 | |
20 | |
15 | |
8 |
User | Count |
---|---|
71 | |
47 | |
46 | |
20 | |
16 |