topic Re: Summarize data calculated at bottom level in DAX Commands and Tips

Summarize data calculated at bottom level

ribaldo — Mon, 18 Jul 2022 10:44:24 GMT

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

Re: Summarize data calculated at bottom level

amitchandak — Mon, 18 Jul 2022 12:50:19 GMT

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

Re: Summarize data calculated at bottom level

ribaldo — Mon, 18 Jul 2022 13:23:47 GMT

amitchandak

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?

Re: Summarize data calculated at bottom level

Anonymous — Mon, 18 Jul 2022 14:24:35 GMT

create the new measure for summarize the table with sumx

Re: Summarize data calculated at bottom level

ribaldo — Tue, 19 Jul 2022 06:48:25 GMT

Anonymous I created the M2 measure that summarize M1, but it doesn't work: