Measure Total Incorrect

[ngbrown24]

Hello,

Please see my data via this link: 

https://mjbaconsulting.sharepoint.com/:u:/g/Corporate/EXKFmIE5jd9LlColX1Nq7NQBFz959BPJwVk7bcpXkqKQWg... 

and my short video to explain what the issue is that is going on. Any help would be greatly appreciated!!

https://www.loom.com/share/53a65e02716046d0811c990013d6b06a 

[Anonymous · Accepted solution]

Hi @ngbrown24.

I have taken a look at your code... 2 pieces of news.

1. The good one is... here's the code that does what you want:

Monthly increase = 
VAR _Prev_month =
    CALCULATE (
        SUM ( 'High Cost Claimant Summary De-I'[Claims Paid] ),
        PREVIOUSMONTH ( 'High Cost Claimant Summary De-I'[Date] )
    )
VAR _Monthlyincrease =
    SUM ( 'High Cost Claimant Summary De-I'[Claims Paid] ) - _Prev_month
VAR _Previousdates =
    CALCULATE (
        SUM ( 'High Cost Claimant Summary De-I'[Claims Paid] ),
        FILTER (
            'High Cost Claimant Summary De-I',
            'High Cost Claimant Summary De-I'[Date]
                < MAX ( 'High Cost Claimant Summary De-I'[Date] )
        )
    )
VAR _Total =
	SUMX(
		VALUES( 'Factor Set-up'[Plan Year Ending] ),
	    CALCULATE (
	        SUM ( 'High Cost Claimant Summary De-I'[Claims Paid] ),
	        LASTDATE ( 'High Cost Claimant Summary De-I'[Date] )
	    )
	)
RETURN
    IF (
        HASONEVALUE ( 'High Cost Claimant Summary De-I'[Date] ),
        IF (
            CONTAINS (
                'Factor Set-up',
                'Factor Set-up'[Plan Year Ending], VALUE ( YEAR ( PREVIOUSMONTH ( 'High Cost Claimant Summary De-I'[Date] ) ) )
            ),
            _Monthlyincrease, // This was not here, the whole expression was duplicated
            SUM ( 'High Cost Claimant Summary De-I'[Claims Paid] ) // this should be made into a measure on its own
        ),
        _Total
    )

2. The bad one is... the model and DAX are NOT good. They violate so many principles of good dimensional design and writing good, performant, and correct DAX that I wouldn't even dare to put this in production. There are even too many violations for me to have space and time here to describe all of them and give reasons behind them.

Sorry about the criticism... but I just couldn't get over it without letting you know. I want you to know as well that you'll have big, big problems with this model down the road (and those who will come after you and will have to maintain it). Not to mention that you will not even be aware many of them at all.

Best

D

[ngbrown24]

@Anonymous Thank you! The criticism is appreciated. I have made a couple of tweaks that I think will help - I've swapped out the "contain" function for a selectedvalue function that I think will work better. It sounds like there are many other issues. Any tips would be appreciated. I'm pretty new to PowerBI (as you can tell). 

Thank you!

[Anonymous]

Please try to learn more about good dimensional design which should always be implemented in professional Power BI projects. You can start with these:

https://www.youtube.com/watch?v=_quTwyvDfG0

https://www.youtube.com/watch?v=78d6mwR8GtA

https://www.sqlbi.com/tv/time-intelligence-in-microsoft-power-bi/

https://radacad.com/basics-of-time-intelligence-in-dax-for-power-bi-year-to-date-quarter-to-date-mon...

The best courses by far on DAX and model design can be found on www.sqlbi.com. I know they are not free (worth every pound spent, though) but NOTHING really compares to them. All my knowledge (and I manage commercial models with more than 500 million rows and 500+ measures in them) comes from The Italians. If you don't want to shell out for these, you should at least read The Definitive Guide to DAX by The Italians. I've read this 5 times already from cover to cover and I know it's not yet over.

By the way, look what can happen if the model is not a proper star-schema:

https://www.sqlbi.com/articles/understanding-dax-auto-exist/

Sadly, these are issues you won't even be able to spot 😞

Lastly, if the model is not correct, DAX will be extremely complex, slooooow and almost unmaintainable. Don't fall into this trap. Many do and fail.

Best
D

[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

[ngbrown24]

Thanks Greg, I actually looked at this post several times today, but it doesn't seem to get me  what I need.