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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors