Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Total of measure is incorrect

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. 

 

 

 

 

 

 
1 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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

9 REPLIES 9
amitchandak
Super User
Super User

@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
Not applicable

Hi @amitchandak ,

 

Thanks for you reply.

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

@Anonymous ,

Can you share sample data and sample output.

Anonymous
Not applicable

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

@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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.