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
IF
Post Prodigy
Post Prodigy

weighted average and exception

Hi,

I want to develop a measure to calculate average and weighted average at the same time. I am using 3 slicers for selection (Month, Group and Class).

If I don't select anything in Class slicer, then it should calculate the weighted average based on the "average" value per class and the number of students. However, Class C should not be considered in the calculation. For example; for the selection of January 2021 in the month slicer and not select anything in Class slicer, it should provide:

ClassA: (240+290)/2=265 and ClassB: (120+60)/2=90 and the weighted average should be:

((265*80)+(90*60))/(80+60) =190

If I select ClassA, then it should provide the result 265, which is the average, not the weighted average.

This is the data.

GoalHighestLowestAverageInvolvedStudentsTotalStudentsGroupClassMonth
240250200240550G1ClassA01.2021
320330280290430G2ClassA01.2021
13014050120720G1ClassB01.2021
80904060340G3ClassB01.2021
3003002002601060G1ClassC01.2021
3003002002601070G4ClassC01.2021
250260200240445G1ClassA02.2021
330340290290535G2ClassA02.2021
13013060130620G1ClassB02.2021
901006070445G3ClassB02.2021
3003002002701050G1ClassC02.2021
3003002002701080G4ClassC02.2021

Thanks in advance!

1 ACCEPTED SOLUTION
v-xiaoyan-msft
Community Support
Community Support

Hi @IF ,

 

Please create measures like below to meet your needs:

Measure = 
VAR _selclass =
    SELECTEDVALUE ( 'Table'[Class] )
VAR _avg =
    CALCULATE (
        AVERAGE ( 'Table'[Average] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Class] = _selclass )
    )
VAR _avg1 =
    IF ( _selclass = "ClassC", BLANK (), _avg )
VAR _wavg =
    SWITCH ( _selclass, "ClassA", _avg1 * 80, "ClassB", _avg1 * 60 ) / 140
RETURN
    _wavg
weighted average = sumx(VALUES('Table'[Class]),[Measure])

 

vcaitlynmstf_0-1639556600004.png

 

A sample PBIX. for your reference is attached.

 

If I have not understood your needs correctly, please do not hesitate to inform me.

Hope it helps,


Community Support Team _ Caitlyn

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

2 REPLIES 2
v-xiaoyan-msft
Community Support
Community Support

Hi @IF ,

 

Please create measures like below to meet your needs:

Measure = 
VAR _selclass =
    SELECTEDVALUE ( 'Table'[Class] )
VAR _avg =
    CALCULATE (
        AVERAGE ( 'Table'[Average] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Class] = _selclass )
    )
VAR _avg1 =
    IF ( _selclass = "ClassC", BLANK (), _avg )
VAR _wavg =
    SWITCH ( _selclass, "ClassA", _avg1 * 80, "ClassB", _avg1 * 60 ) / 140
RETURN
    _wavg
weighted average = sumx(VALUES('Table'[Class]),[Measure])

 

vcaitlynmstf_0-1639556600004.png

 

A sample PBIX. for your reference is attached.

 

If I have not understood your needs correctly, please do not hesitate to inform me.

Hope it helps,


Community Support Team _ Caitlyn

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

 

Hello,

Thanks for helping me. If I don't select the class, the result is correct. However, if I select the class (for example class A), then I expect to see the average not weighted average. Then the result should be (240+290)/2 which is 265.

paTest1.jpg

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.