Forum Discussion

Anonymous

Not applicable

6 years ago

Solved

Calculations with missing data

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
6 years ago
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.

3 Replies

hnguy71
Super User
6 years ago
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
  Not applicable
  6 years ago
  Thank you. I've reorganised the data as you suggested. I'll play with it now.
  - Anonymous
    Not applicable
    6 years ago
    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.