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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Wanghongyu
New Member

Need help to calculate weighted average

I have a database similar to below left table. But I would like to summariz it to right hand side pivot.

I manage to get two measure in data model one is "sum of schedule" another one is "weightage".

 

sum of schedule:=SUM('table'[schedule])

weightage:=DIVIDE([sum of schedule],CALCULATE([sum of schedule],ALLSELECTED()))

 

I can do a multiplication to get all individual values as displayed in cells J4 to J6. However, I couldn't get

value J7 which is the sum of J4 to J6 by using DAX function. 

 

Appriciate if anyone could help.

 

data.PNG

 

1 ACCEPTED SOLUTION

Hi @Wanghongyu,

 

You can try to use below measure to achieve your requirement:

 

Current Schedule = 
VAR current_machine =
    LASTNONBLANK ( Sheet2[Machine], [Machine] )
VAR summary =
    ADDCOLUMNS (
        SUMMARIZE (
            ALLSELECTED ( Sheet2 ),
            [Machine],
            "Total Schedule", SUM ( Sheet2[Schedule] ),
            "Percent", SUM ( Sheet2[Schedule] ) / SUMX ( ALLSELECTED ( Sheet2 ), [Schedule] )
        ),
        "Current", [Total Schedule] * [Percent]
    )
RETURN
    IF (
        COUNTROWS ( Sheet2 ) <> COUNTROWS ( ALLSELECTED ( Sheet2 ) ),
        MAXX ( FILTER ( summary, [machine] = current_machine ), [Current] ),
        SUMX ( summary, [Current] )
    )

12.PNG

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
v-shex-msft
Community Support
Community Support

HI @Wanghongyu,

 

Please share the sample pbix file to test, I'm not so sure where the machine column come from and relationship to current tables.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Sorry. Machine is in column A and it's not captured before.

 

Capture.PNG

Hi @Wanghongyu,

 

You can try to use below measure to achieve your requirement:

 

Current Schedule = 
VAR current_machine =
    LASTNONBLANK ( Sheet2[Machine], [Machine] )
VAR summary =
    ADDCOLUMNS (
        SUMMARIZE (
            ALLSELECTED ( Sheet2 ),
            [Machine],
            "Total Schedule", SUM ( Sheet2[Schedule] ),
            "Percent", SUM ( Sheet2[Schedule] ) / SUMX ( ALLSELECTED ( Sheet2 ), [Schedule] )
        ),
        "Current", [Total Schedule] * [Percent]
    )
RETURN
    IF (
        COUNTROWS ( Sheet2 ) <> COUNTROWS ( ALLSELECTED ( Sheet2 ) ),
        MAXX ( FILTER ( summary, [machine] = current_machine ), [Current] ),
        SUMX ( summary, [Current] )
    )

12.PNG

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

This is excatly what I want. Thanks so much

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.