Forum Discussion
MAX DAX Function giving incorrect sum
Hi,
I have a requirement where I have to multiply an amount by 5 if the current month is the first month of quarter, else multiply by 4. Ex: if the month is January(1), April(4), July(7), October(10) then multiply by 5, else multiply by 4 for the rest of the months.
I have written below DAX formula for it:
This gives proper result in most cases where all the months are either start of the month (any combination of 1, 4, 7 ,10) or the other months (any combination of 2, 3, 5, 6, 8, 9, 11, 12).
But gives incorrect result when the months selected are a combination of start of quarter months and other months (ex: 10 and 11).
Below is the snapshot of the result:
This example should sum to 8,930,815.3 but currently it shows 7,948,491.08.
Kindly advise what can be done to achieve the correct results, or if anything is not right in the applied DAX formula.
Thanks in advance!
Hello! I would try the following:
RETURNSUMX( 'Calendar Master',
IF( MAX( 'Calendar Master'[Fiscal month num])IN(1,4,7,10), DIVWEEKS*5, DIVWEEKS*4)
)If that doesn't work, I'd try checking the filters applied to the sheets and visuals.
Best regards!
2 Replies
- BrunoTaliniFrequent Visitor
Hello! I would try the following:
RETURNSUMX( 'Calendar Master',
IF( MAX( 'Calendar Master'[Fiscal month num])IN(1,4,7,10), DIVWEEKS*5, DIVWEEKS*4)
)If that doesn't work, I'd try checking the filters applied to the sheets and visuals.
Best regards! - Greg_DecklerCommunity Champion
gamer_77768 First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
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
Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8