Latest Enterprise DNA Initiatives


Incorrect table calculations

Hi everyone,

I have been grappling with this for a few days and looked through various ‘Incorrect Total’ tutorials, but I am not having much luck so far.

A PBIX file is attached.

TotalExample.pbix (46.0 KB)

The model is simplified, but I think it has everything needed to replicate the problem. There are two dimension tables (student name and subject) and two fact tables (report data and predicted grades). They have 1 to many relationships between them.

For each subject, a student gets report grades at several points throughout the year. They also have grade predictions for most subjects. My calculations are:

  1. Average predicted grade
  2. Average trajectory (report) grade
  3. Value-add (trajectory minus predicted grade)-if a positive score, students are doing better than expected.
  4. A student’s average grade for all their subjects for a selected report cycle
  5. Difference between the report grade for a subject and the average for all subjects
  6. Value add for all subjects-the average trajectory minus predicted grade for a student for a selected report cycle
  7. Value add difference between selected subject and all subjects

Not all subjects have predicted grades. Not all students have all the subjects. Not all report cycles have trajectory grades for all subjects. The calculations mainly work on a row-by-row basis, but the totals are incorrect in pivot tables and the value-add for all subjects is incorrect.

Any help would be much appreciated.

Thank you.
Matt

Hi @corkemp - Can you help with the calculation that is giving Incorrect result and what is expected. Based on the DAX Formulas and Data available calculation seems correct.

Thanks
Ankit J

Hi @corkemp, just following up on the request of @ankit. Thanks!

Thanks for taking a look at it, Ankit.

The trajectory - prediction total in the matrix containing student and subject is incorrect (top right of page 1). In Excel, the average of the values is 0.33 for Student 1, whilst in the matrix it is 0.48 (this is with filters removed for the report cycle).

This incorrect value carries over to the trajectory-predicted measure (all subjects). It should be 0.33. Consequently, it also carries over to the value-add difference between subjects (trajectory-predicted grade MINUS trajectory-predicted (all subjects)).

Thanks
Matt

Hi @Corkemp - This is how Power BI works and as per calculations and data available Output is correct.

You will get .33 as total if you take Average of [Trajectory-predicted] for individual Subjects. However, when evaluating at Student level i.e. Top Hierarchy it will take Average of all Subjects combined without taking into individual Subjects.

To get output as .33 at Student level, need to create formula like below.

Trajectory-predicted_new =

IF(ISINSCOPE(Subjects[Subjects]),[Trajectory-predicted],AVERAGEX(SUMMARIZE(Subjects,Subjects[Subjects],“TP_AVG”,[Trajectory-predicted]),[TP_AVG]))

Thanks
Ankit J

Hi @corkemp, did the response provided by @ankit help in solving your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

Thank you Ankit. This is working - I’ll try apply it to my real data and see if it does what it should do!

Thanks again.
Matt

Hi Ankit,

When I use your solution on my main dataset it isn’t working as expected. Please see the example in the image. The totals for the column that subtracts one value from another is doing it on the totals when I want it to average the values in the column so that blank values are not included.

This is the DAX I have used:

VAR AverageGrade = CALCULATE ( AVERAGE ( 'ExamData'[Grade (standardised)] ), 'ExamData'[Exam Category]="External Exam" )

VAR AveragePrediction = [Predicted grades measure]
RETURN

VAR GradePrediction = 
IF(
    AverageGrade=BLANK() 
        || AveragePrediction=BLANK(),
    BLANK(),
AverageGrade-AveragePrediction
   )
RETURN

IF(
    ISINSCOPE(SubjectInfo[Subject]),
    GradePrediction,
    AVERAGEX(SUMMARIZE(SubjectInfo,SubjectInfo[Subject],"TP_AVG",GradePrediction),[TP_AVG])
    )

image

What am I missing?

Thank you for your your help.
Matt