cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.1 3.2 3.3 7.1 7.2 7.3 7.4 7.5 10.2 Baseline Exam 80% 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.

4 REPLIES 4
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...)

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.
New Member

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

 Username AttemptDate Exam Section Score Sally 1/1/2020 Baseline 1.1 50% Sally 1/1/2020 Baseline 1.2 25% Sally 1/1/2020 Baseline 1.3 100% Sally 1/1/2020 English 101 1.1 75% Sally 1/1/2020 English 101 1.2 80% Sally 1/1/2020 English 101 1.4 90% Sally 1/1/2020 English 101 1.7 95%

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!

Community Support

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' ),
[AttemptDate],
[Section],
"cExam", COUNTA('Table'[Exam])
),
[cExam] >= 2
)
)
RETURN
IF (
currSelection IN _list,
CALCULATE (
AVERAGE ( 'Table'[Score] ),
ALLSELECTED ( 'Table' ),
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.
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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors