cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tylerr2142
New Member

Calculate Average for Common Columns in Matrix

I would like to calculate the average of the baseline assessment for just the common standards. In this case, it should be the average of 7.1, 7.2 and 7.3. I would also like to calculate (nonbaseline) exam score for common standards. Again, just 7.1, 7.2 and 7.3. These will go into single value cards to show improvement from common standards.

 3.13.23.37.17.27.37.47.510.2
Baseline Exam80%60%100%40%80%100%  100%
101 Exam   100%25%100%75%100% 

 

Unfortunately, I cannot share the PBIX file due to confidential exam data.

 

Thanks in advance!

4 REPLIES 4
v-shex-msft
Community Support
Community Support

Hi @tylerr2142,

I also think share some same data will help us clarify your data structure and test to coding formula.

In fact, we do not need the real data, you can keep the raw table structure and build some dummy data to share. (e.g. use random value to replace the raw numerical values, replace sensitive string with test,123...)

How to Get Your Question Answered Quickly 

BTW, where did these common standards from? Based on calculations or specific by filters?

Regards,

Xiaoxin Sheng

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

@amitchandak @v-shex-msft 
The data structure looks like the below:

UsernameAttemptDateExamSectionScore
Sally1/1/2020Baseline1.150%
Sally1/1/2020Baseline1.225%
Sally1/1/2020Baseline1.3100%
Sally1/1/2020English 1011.175%
Sally1/1/2020English 1011.280%
Sally1/1/2020English 1011.490%
Sally1/1/2020English 1011.795%

 

I want to be able to calculate the improvement from Baseline exam to other Non-Baseline exams with identical sections. (I.E: a score of 50% for baseline section 1.1 to the 75% for English 101 section 1.1. 

 

Thanks for the help!

Hi @tylerr2142,

Did you mean you want to calculate the average 'Score' across multiple exams based on the most general 'Section' which existed all exams, right?

If that is the case, you can try to use the following measure expression to replace the scope field to use in matrix and confirm if it works on your side:

Measure = 
VAR currSelection =
    MAX ( 'Table'[Section] )
VAR _list =
    CALCULATETABLE (
        VALUES ( 'Table'[Section] ),
        FILTER (
            SUMMARIZE (
                ALLSELECTED ( 'Table' ),
                [Username],
                [AttemptDate],
                [Section],
                "cExam", COUNTA('Table'[Exam])
            ),
            [cExam] >= 2
        )
    )
RETURN
    IF (
        currSelection IN _list,
        CALCULATE (
            AVERAGE ( 'Table'[Score] ),
            ALLSELECTED ( 'Table' ),
            VALUES ( 'Table'[Username] ),
            VALUES ( 'Table'[AttemptDate] ),
            VALUES ( 'Table'[Section] )
        )
    )

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
amitchandak
Super User
Super User

@tylerr2142 , are these column ?

7.1, 7.2 and 7.3

 

The information you have provided is not making the problem clear to me. Can you please explain with an example.
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Appreciate your Kudos.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!