Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Goal | Highest | Lowest | Average | InvolvedStudents | TotalStudents | Group | Class | Month |
240 | 250 | 200 | 240 | 5 | 50 | G1 | ClassA | 01.2021 |
320 | 330 | 280 | 290 | 4 | 30 | G2 | ClassA | 01.2021 |
130 | 140 | 50 | 120 | 7 | 20 | G1 | ClassB | 01.2021 |
80 | 90 | 40 | 60 | 3 | 40 | G3 | ClassB | 01.2021 |
300 | 300 | 200 | 260 | 10 | 60 | G1 | ClassC | 01.2021 |
300 | 300 | 200 | 260 | 10 | 70 | G4 | ClassC | 01.2021 |
250 | 260 | 200 | 240 | 4 | 45 | G1 | ClassA | 02.2021 |
330 | 340 | 290 | 290 | 5 | 35 | G2 | ClassA | 02.2021 |
130 | 130 | 60 | 130 | 6 | 20 | G1 | ClassB | 02.2021 |
90 | 100 | 60 | 70 | 4 | 45 | G3 | ClassB | 02.2021 |
300 | 300 | 200 | 270 | 10 | 50 | G1 | ClassC | 02.2021 |
300 | 300 | 200 | 270 | 10 | 80 | G4 | ClassC | 02.2021 |
Thanks in advance!
Solved! Go to Solution.
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])
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.
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])
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.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |