Forum Discussion
Correctly Summing Min Max for visual
I am working on a measure and when I check the totals the data is not adding up to the total box.
my calculations should return -583k and in the table it shows that but the row coulum totals and other visuals i have show -413k so essentially missing (or adding 170k). I have narrowed it down to the Min max functions i am using and know that the 170k is coming from customer 5 in the data set as they are in a positive number. I am confident the measure plan in my code is correct as it lines up with my calculations in Excel. I beleive the issue is when i am using Min and Max. Thoughts and help?
MTH Plan =
VAR TRACKING_START =
MAX(Calendar_Table[PRJ_TRACK_DATE])
VAR EOM =
MAX(Calendar_Table[EOM])
VAR DAY_IN_MONTH =
DAY(EOM)
VAR DAY_INITATIVE =
MAX(0,MIN(EOM-TRACKING_START,DAY_IN_MONTH))
VAR DAY_TRACKING =
MIN(0,DIVIDE(DAY_INITATIVE,7)*[Plan])
RETURN
if(DAY_TRACKING>0,0,DAY_TRACKING)
Tracking_Start: is when I start tracking that Customer
EOM: Selected EOM
DAY_IN_MONTH: Just the total days in selected month
Day Initative: how many days are being tracked in that month. So if i start tracking a customer in that current month it would show how many active days for the selected month. So in Customer 3 they would be null for September and 30 days for the month of November
Day_tracking: The days per week
Month Plan is the monthly plan based on the Day Tracking
| CUSTOMER | TRACKING_START | EOM | DAY_IN_MONTH | DAY_INITATIVE | DAY_TRACKING | MONTH PLAN |
| Customer 1 | 11/13/2021 | 10/31/2022 | 31 | 31 | 4.43 | 0 |
| Customer 2 | 2/26/2022 | 10/31/2022 | 31 | 31 | 4.43 | -17000 |
| Customer 3 | 10/1/2022 | 10/31/2022 | 31 | 30 | 4.29 | 0 |
| Customer 4 | 2/26/2022 | 10/31/2022 | 31 | 31 | 4.43 | -253000 |
| Customer 5 | 4/30/2022 | 10/31/2022 | 31 | 31 | 4.43 | 0 |
| Customer 6 | 4/9/2022 | 10/31/2022 | 31 | 31 | 4.43 | 0 |
| Customer 7 | 2/12/2022 | 10/31/2022 | 31 | 31 | 4.43 | -184000 |
| Customer 8 | 9/17/2022 | 10/31/2022 | 31 | 31 | 4.43 | -129000 |
1 Reply
- Greg_DecklerCommunity Champion
Anonymous 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