Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Anonymous
Not applicable

Calculations with missing data

Hi,

I have got a simple table of student attendance (around 50 rows) looking as follows:

 

CourseGroupCourseCodePeriod 1 - ActualPeriod 1 - TargetPeriod B - ActualPeriod B - TargetPeriod C - ActualPeriod C - Terget
AZ0001255034584358
BZ0002152035403350
AZ0003122315251622
BZ00043040354533

50

 

I've created a measure for each period to calculate the percentage of achieving the target:

 
CalcPeriod1 = sumx( Registers_1920, Registers_1920[Actual 1] / Registers_1920[Target 1])
 
And set the formatting to % in the Modelling tab.
 
The measure works fine in a table for each course code.
 
If, however, I use the Matrix table by CourseGroup, it seems to be adding up the % (resulting in % higher than 100)
 
I would have thought that with the above measure, it would do the following:
For Course Group A: (25+12)/(50+23) which is what I'd like to do for the Matrix Table.
 
Please advise.
 
Thank you
 

 

 

1 ACCEPTED SOLUTION
hnguy71
Memorable Member
Memorable Member

@Anonymous 
I think there's a fundamental thing you have to do with your data modeling first. Unpivot your data into similar fashion:

unpivot.png

Next, you need to create generic measures:

TotalValue = SUM(School[Value])

Actuals = CALCULATE([TotalValue], School[Attribute] = "Actual") 

Targets = CALCULATE([TotalValue], School[Attribute] = "Target") 

Delta = DIVIDE([Actuals], [Targets])

And you should be able to achieve your desired results:
delta.png

If this is not the answer you were looking for please let me know.

 



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

View solution in original post

3 REPLIES 3
hnguy71
Memorable Member
Memorable Member

@Anonymous 
I think there's a fundamental thing you have to do with your data modeling first. Unpivot your data into similar fashion:

unpivot.png

Next, you need to create generic measures:

TotalValue = SUM(School[Value])

Actuals = CALCULATE([TotalValue], School[Attribute] = "Actual") 

Targets = CALCULATE([TotalValue], School[Attribute] = "Target") 

Delta = DIVIDE([Actuals], [Targets])

And you should be able to achieve your desired results:
delta.png

If this is not the answer you were looking for please let me know.

 



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!
Anonymous
Not applicable

Thank you. I've reorganised the data as you suggested. I'll play with it now.

Hi @Anonymous ,

 

Have your problem be solved? Please consider accept the answer as a solution if it worked.

 

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.