Total of measure is incorrect

[Anonymous]

Hi everyone,

The following formula is working, except for the outcome it shows for Total:

XXX =

VAR MaxYear = YEAR(MAX('Table'[Date]))

VAR MaxMonth = MONTH(MAX('Table'[Date]))

VAR T1 = GROUPBY(FILTER('Table';

[Year]=MaxYear&&MONTH([Date])=MaxMonth&&[Value]=-1);

[Location];"TEST";COUNTX(CURRENTGROUP();[Value]))

RETURN

SUMX(T1;[TEST])

I think I only need a small adjustment, but I couldn't figure out how to apply this. The result of Total should be 18. 

[amitchandak]

@Anonymous ,

Try like

XXX =
VAR MaxYear = YEAR(MAX('Table'[Date]))
VAR MaxMonth = MONTH(MAX('Table'[Date]))

RETURN
SUMX(summarize(FILTER('Table';[Year]=MaxYear&&MONTH([Date])=MaxMonth&&[Value]=-1);'Table'[Location];"_TEST";COUNT([Value])),[_TEST])

[Anonymous]

Hi @amitchandak ,

Thanks for you reply.

I received the exact same results with your formula, so Total still shows 1. 

[amitchandak]

@Anonymous ,

Can you share sample data and sample output.

[Anonymous]

@amitchandak 

Sure, here is the Excel file:

https://drive.google.com/file/d/13HR8-JiYunuB0aFqfd1H3iUGRWjLYIE7/view?usp=sharing

And here is the PBI file:

https://drive.google.com/file/d/1-qDDE9Tccf7UurvCBZc-i0nlZe2C0Ovb/view?usp=sharing

[amitchandak]

@Anonymous , @Greg_Deckler  has already provided the solution. So no need for me to check

[Greg_Deckler · Accepted solution]

@Anonymous - As I have been trying to point out, the solution is this Quick Measure, Measure Totals, The Final Word:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

The implementation in your situation is:

Greg_Deckler = 
    IF(
        HASONEVALUE('Inspection 2016-2020'[Name]),
        [Most recent values -1],
        SUMX(
            SUMMARIZE(
                'Inspection 2016-2020',
                [Name],
                "Measure",[Most recent values -1]
            ),
            [Measure]
        )
    )

PBIX is attached.

[Greg_Deckler]

Sample data would really help. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

But still, This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[Greg_Deckler]

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[Anonymous]

Something went wrong with including the image of the table. So, I'll do it in this way: Location XXX a 4 b 2 c 3 d 1 e 3 f 2 g 3