DAX Query Measure not displaying all percentages on a line graph

[Anonymous]

Hi there, I hope I have posted this in the correct place. 

I have been tasked to create a stats report for an awarding organisation. They would like me to put together a line graph that compares:

1. A exam centres marks out of 100 as a percentage for each value, over all time. e.g. 3.7% scored 67/100, 4.8% scored 84/100 etc
2. An examiners marking out of a 100, which can be filtered by a date range slider and the percentage line will adjust accordingly

Context: This report will enable our moderators to compare how an examiner has been performing in the past month or two, against the centres performance over all time. This will mean that our moderators will be able to see whether an examiner is marking too harshly or too leniently against the centres overall average.

To calculate the percentage of all marks out of 100 for a given centre, I have implemented the two following measures:

TotalExamsAtCentre =
CALCULATE (
    DISTINCTCOUNT ( 'ExaminerAndCentre'[ResultId] ),
    ALL ( 'ExaminerAndCentre'[Result Added Date] ),
    ALL ( 'ExaminerAndCentre'[Examiner] )
)

CentrePercentage =
[TotalExamsAtCentre]
    / CALCULATE (
        [TotalExamsAtCentre],
        ALL ( 'ExaminerAndCentre'[Mark of 100] ),
        ALL ( 'ExaminerAndCentre'[Result Added Date] )
    )

The ALL [Result Added Date] filter is important in these expressions as I don't want the date range slider to be affecting the stats, as they need to be for all time. 

For the examiner i have implemented two similar measures:

TotalExams =
COUNT ( ExaminerAndCentre[ResultId] )

ExaminerPercentage =
[TotalExams]
    / CALCULATE ( [TotalExams], ALL ( 'ExaminerAndCentre'[Mark of 100] ) )

These measures are this way, as the percentage will vary depending on what slicer filters have been applied. 

Here is a screen shot of my current report

 The examiner line is perfectly correct, the percentage adds up to 100. However, the centre line does not. This is because the line graph only creates a matching point where one appears in the Examiner line, so there are a lot of missing values in the blue line and I am struggling to find a solution for this. This issue happens when the Result Added Date Slicer has been reduced so that the examiner has only marked a couple of exams, limiting the number of Marks out of 100 . Is this issue related to my DAX expessions?

Feel free to download the report here - 

https://rockschoolproduction.blob.core.windows.net/public/Examiner%20and%20Centre%20Statistics%20Exa...

Thank you for taking the time to read this and for any help you can give. This is my first time posting on here, and I hope this post is informative to my issue. 

[Anonymous]

Change the model to be a proper star schema and many of your headaches will be gone in no time. DAX will be much, much simpler and there will not be any hidden bugs in the code. And the code will be much faster. The key to simple, robust and correct DAX is a star-schema model.

[dedelman_clng]

Hi @Anonymous  -  I realize this is an old post, but I just recently discovered this particular sub-forum 🙂

I wasn't able to find the pbix in the zip file you linked, but from what you're describing, have you tried using a clustered column chart instead of lines? Lines are generally used to show a trend evolving over time, whereas the columns work better with a non-date X-axis.

Hope this helps

David