Calculations with missing data

[Anonymous]

Hi,

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

CourseGroup	CourseCode	Period 1 - Actual	Period 1 - Target	Period B - Actual	Period B - Target	Period C - Actual	Period C - Terget
A	Z0001	25	50	34	58	43	58
B	Z0002	15	20	35	40	33	50
A	Z0003	12	23	15	25	16	22
B	Z0004	30	40	35	45	33	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

[hnguy71 · Accepted solution]

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

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:

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

[Anonymous]

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

[Anonymous]

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.