cancel
Showing results for
Did you mean:
Frequent Visitor

## How to calculate the Mean

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 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 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
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: How to calculate the Mean

Hi @Vishnoo ,

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

```Mean =
VAR summary =
FILTER (
SUMMARIZE (
Sheet1,
[School Code],
[Score],
"CYTS", CALCULATE (
DISTINCTCOUNT ( Sheet1[Candidate ID] ),
FILTER (
ALLSELECTED ( Sheet1 ),
&& 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

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

For learning resources/Release notes, please visit: | |
Community Support Team

## Re: How to calculate the Mean

Hi @Vishnoo ,

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

```Mean =
VAR summary =
FILTER (
SUMMARIZE (
Sheet1,
[School Code],
[Score],
"CYTS", CALCULATE (
DISTINCTCOUNT ( Sheet1[Candidate ID] ),
FILTER (
ALLSELECTED ( Sheet1 ),
&& 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

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

For learning resources/Release notes, please visit: | |

Announcements

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

#### PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 21 members 788 guests
Recent signins: