Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
ribaldo
Frequent Visitor

Summarize data calculated at bottom level

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"

 

ribaldo_0-1658133514213.png

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

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

create the new measure for summarize the table with sumx 

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

ribaldo_0-1658213293672.png

 

ribaldo
Frequent Visitor

@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:

ribaldo_0-1658150517665.png

I did something wrong?

 

amitchandak
Super User
Super User

@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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors