Incorrect Total in Matrix Visual

[ArchStanton1]

Hi,

(I have edited this because the problem is actually the reverse of what I wrote originally).

Edited: The correct total for 6-9mths in the visual below is 78 but the matrix is including 53 under Adjudication which would / should take the total for the row to 131. All the other age profile numbers are correct apart from 6-9 mths.

The AgeProfile2 is a Calculated Column, the code is:

Age Profile2 = 
if( 'Cases'[Case Length (Validation Date)] < 91.3, REPT(UNICHAR(8203),9)&" 0-3 Mths",
    if('Cases'[Case Length (Validation Date)] < 182.5, REPT(UNICHAR(8203),8)&" 3-6 Mths",
        if('Cases'[Case Length (Validation Date)] < 273.5, REPT(UNICHAR(8203),7)&" 6-9 Mths",
            if('Cases'[Case Length (Validation Date)] < 365.25, REPT(UNICHAR(8203),6)&" 9-12 Mths",
                if('Cases'[Case Length (Validation Date)] < 456.25, REPT(UNICHAR(8203),5)&" 12-15 Mths",
                    if('Cases'[Case Length (Validation Date)] < 547.5, REPT(UNICHAR(8203),4)&" 15-18 Mths",
                        if('Cases'[Case Length (Validation Date)] < 638.75, REPT(UNICHAR(8203),3)&" 18-21 Mths",
                            if('Cases'[Case Length (Validation Date)] < 730.5, REPT(UNICHAR(8203),2)&" 21-24 Mths",
                                REPT(UNICHAR(8203),1)&" 24+ Mths"))))))))

And the measure I'm using to calculate the numbers is here:

Closed = CALCULATE(
    TOTALYTD(COUNT('Cases'[Case Number]),'Cases'[Resolution Date],"31/03"),
    'Cases'[statecode] = "Resolved")

Can anyone shed some light on why everything is correct for all other Teams apart from Adjudication and why its only wrong for 6-9 mths?

Incidentally, when I recreate the visual using filters and distinct counds of IDs, the 53 in Adjudication do not appear anymore.

Why is my 'Closed' measure introducing these 53?

Thanks

[rubayatyasmin]

@ArchStanton1 

1. Ensure 'Cases'[Case Length (Validation Date)] values for Adjudication are indeed in the 6-9 months range.

2. Ensure Adjudication cases are marked as "Resolved" and their 'Cases'[Resolution Date] falls within the current fiscal year.

3. Make sure no filters exclude the 6-9 months Adjudication cases from your visualization.

4. Confirm the missing Adjudication cases are in your dataset and don't have anomalies.

5. Review relationships to ensure no inadvertent filtering.

[ArchStanton1]

I've carried out checks 1-3 that you suggested and they are fine. Additionally, I have isolated the 78 6-9 mths in the data table by applying filters to relevant columns. 

So I'm not quite sure why there are 53 Adjudications (6-9mths) being included in the matrix? They are not being counted in the row & column totals, which I believe is a good thing.

They shouldn't be in the visual from what I can see, I'm at a loss why they are there.

[rubayatyasmin]

Can you provide additional demo data or pbix file?

[ArchStanton1]

Unfortunately I cannot provide a pbix file as its directly connected to large complex live system.

Would you agree that the measure I have provided should in no way introduce 'phantom' numbers into my visual, bearing in mind that the report is filter free? The 53 was excluded in the totals which is even more bizarre.

As I've mentioned already, I have duplicated the report and re-built it using filters and distinct counts I it works, the red circle below is now correct as a blank.

Have you seen behaviour like this before?

[rubayatyasmin · Accepted solution]

I've seen this kind of behaviour but that was a simple solution. That was of typing mistake. But this is completely different. I am not certain about the issue. I won't be able to help without seeing any data, but guess it's not possible. 

best of luck. Let us know here if you find the root cause. 

 BR,

Rubayat

[ArchStanton1]

Will do, thanks for your help.