cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Vishnoo Frequent Visitor
Frequent Visitor

How to calculate the Mean

I have the below Model.

 

Table 1: (Sheet 1)

Model-Table1.JPGSheet 1 Table

Table 2: (Score)

 

Model-Table2.JPGScore Table

 

I have the following measures to display the visual below.

 

visual.JPGVisual

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

Accepted Solutions
Community Support Team
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 (
        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

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: | |
1 REPLY 1
Community Support Team
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 (
        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

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: | |

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

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

Top Ideas
Users Online
Currently online: 21 members 788 guests
Please welcome our newest community members: