How to calculate the Mean

[Vishnoo]

I have the below Model.

Table 1: (Sheet 1)

Sheet 1 Table

Table 2: (Score)

Score Table

I have the following measures to display the visual below.

Visual

cy total School =

var cy = MAX(Sheet1[Academic Year])

var Totalcount =

CALCULATE(DISTINCTCOUNT(Sheet1[Candidate ID]), Sheet1[Academic Year] = cy, Sheet1[Score] >120)

return

Totalcount +0

LSAT Percentage School =

VAR LSAT =

SELECTEDVALUE ( score[Score] )

VAR TotalPercent =

DIVIDE (

CALCULATE (

DISTINCTCOUNT ( Sheet1[Candidate ID] ), Sheet1[Score] <> BLANK(),

FILTER ( ALL(score), score[Score] < LSAT )

),

CALCULATE (

DISTINCTCOUNT ( Sheet1[Candidate ID] ), Sheet1[Score] <> BLANK(),

ALL(score[Score])

)

)

Return

(TotalPercent + 0)

cy tot nat 1 =

VAR cy = MAX(Sheet1[Academic Year])

VAR LSAT = SELECTEDVALUE(score[Score])

VAR tab = CALCULATETABLE(ALLEXCEPT(Sheet1, Sheet1[Academic Year]))

VAR val = CALCULATE(DISTINCTCOUNT(Sheet1[Candidate ID]), FILTER(tab, Sheet1[Score] = LSAT))

Return

val + 0

LSAT Percentage National =

VAR LSAT =

SELECTEDVALUE ( score[Score] )

VAR TotalPercent =

DIVIDE (

CALCULATE (

DISTINCTCOUNT ( Sheet1[Candidate ID] ), Sheet1[Score] <> BLANK(), ALL(Sheet1[School Code]),

FILTER ( ALL(score), score[Score] < LSAT )

),

CALCULATE (

DISTINCTCOUNT ( Sheet1[Candidate ID] ), Sheet1[Score] <> BLANK(), ALL(Sheet1[School Code]),

ALL(score[Score])

)

)

Return

(TotalPercent + 0)

What i want to calculate is a measure displaying the Mean for School & National.

It should be the sum of (score * respective distinct count of applicants) / sum of all distinct count of applicatants.

Eg: National Mean = 180 * 2 + 179* 1 + 177 * 1 + 176 * 4 + ......... = 2913 / 17 = 171.35

similarly School Mean for school code 1111 = 180 * 1 + 179 * 1 + 176 * 3 + 172 * 1 + ....... = 1394 / 8 = 174.25

I tried to calculate using this measure, but it is giving me only the score specific and not the total Mean.

MEAN =
VAR LSAT =
SELECTEDVALUE ( score[Score] )
VAR Total =
DIVIDE (
SUM(
CALCULATE (
DISTINCTCOUNT ( Sheet1[Candidate ID] ), Sheet1[Score] <> BLANK(), ALL(Sheet1[School Code]),
FILTER ( ALL(score), score[Score] = LSAT ) * LSAT)
),
CALCULATE (
DISTINCTCOUNT ( Sheet1[Candidate ID] ), Sheet1[Score] <> BLANK(), ALL(Sheet1[School Code]),
ALL(score[Score])
)
)
Return
Total

[v-shex-msft · Accepted solution]

Hi @Vishnoo ,

You can try to use following measure if it suitable for your scenario:

Mean =
VAR summary =
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE (
                Sheet1,
                [School Code],
                [Academic Year],
                [Score],
                "CYTS", CALCULATE (
                    DISTINCTCOUNT ( Sheet1[Candidate ID] ),
                    FILTER (
                        ALLSELECTED ( Sheet1 ),
                        Sheet1[Academic Year] = EARLIER ( Sheet1[Academic Year] )
                            && Sheet1[Score] > 120
                    )
                )
            ),
            "SxCYTS", [Score] * [CYTS]
        ),
        [School Code] IN ALLSELECTED ( Sheet1[School Code] )
    )
RETURN
    AVERAGEX ( summary, [SxCYTS] )

If above not help, please share some sample data for test.

Regards,

Xiaoxin Sheng