Getting summation of Month to Date column

[cho]

Hi Experts,

This is a long and complicated question.

I have a table (Account) with Date, Business, Site ID and Sales. There are multiple Site IDs for one Business. And I have a calendar table with Dates (Date). I need to take cumulative daily sales by site ID for a month. That is, month-to-date values. Similar thing should be done to quarter-to-date, but I will start with month-to-date.

So I added a measure:

MTD Sales = TOTALMTD(SUM(Account[Sales]),Date[DATE])

But, finance team needs to see all figures in (+). Since there are (-) values, I modified the measure to get all values as positives.

MTD Sales (+)= IF(TOTALMTD(SUM(Account[Sales]),Date[DATE])<0,TOTALMTD(SUM(Account[Sales]),Date[DATE])*(-1),TOTALMTD(SUM(Account[Sales]),Date[DATE]))

Then I get the required results on Site ID level. But when it comes to Business level, I need to get the summation of 'MTD Sales' on Site ID level. But what Power BI does is, apply the same logic for Business level as well.

Unforetunately this forum does not allow me to attach my pbix file. But to make things clear, Pls refere below tables.

This table contains the summarized version on Site ID level. I created a table visual based on my measures. This gives required result.

Business	Site ID	Date	Sales	MTD Sales	MTD Sales (+)
RnD	ABC	1/3/2023	(167)	(167)	167
RnD	ABC	1/4/2023	(42)	(209)	209
RnD	ABC	1/5/2023	(183)	(392)	392
RnD	ABC	1/6/2023	(48)	(440)	440
RnD	ABC	1/7/2023	450	10	10
RnD	ABC	1/9/2023	411	421	421
RnD	ABC	1/10/2023	(754)	(333)	333
RnD	ABC	1/11/2023	411	78	78
RnD	ABC	1/31/2023	556	634	634
RnD	XYZ	1/4/2023	(920)	(286)	286
RnD	XYZ	1/5/2023	1,312	1,026	1,026
RnD	XYZ	1/6/2023	(920)	106	106
RnD	XYZ	1/7/2023	1,312	1,418	1,418
RnD	XYZ	1/9/2023	(920)	498	498
RnD	XYZ	1/30/2023	1,526	2,024	2,024
RnD	XYZ	1/31/2023	(920)	1,104	1,104

But when I get a visual on Business level I get this:

Business	Date	Sales	MTD Sales	MTD Sales (+)
RnD	1/3/2023	(167)	(167)	167
RnD	1/4/2023	(962)	(1,129)	1,129
RnD	1/5/2023	1,129	-	-
RnD	1/6/2023	(968)	(968)	968
RnD	1/7/2023	1,762	794	794
RnD	1/9/2023	(509)	285	285
RnD	1/10/2023	(754)	(469)	469
RnD	1/11/2023	411	(58)	58
RnD	1/30/2023	1,526	1,468	1,468
RnD	1/31/2023	(364)	1,104	1,104

I know PowerBI does the correct thing. But I want the total of Site ID level figures on my Business level table like this:

Business	Date	Sales	MTD Sales	MTD Sales (+)
RnD	1/3/2023	(167)	(167)	167
RnD	1/4/2023	(495)	(662)	662
RnD	1/5/2023	634	(28)	28
RnD	1/6/2023	(334)	(362)	362
RnD	1/7/2023	1,428	1,066	1,066
RnD	1/9/2023	919	1,985	1,985
RnD	1/10/2023	(333)	1,652	1,652
RnD	1/11/2023	78	1,730	1,730
RnD	1/30/2023	2,024	3,754	3,754
RnD	1/31/2023	1,738	5,492	5,492

Can someone please help me. Hope this is clear.

[Sahir_Maharaj]

Hello @cho,

To get the total of Site ID level figures on your Business level table, you can modify the MTD Sales (+) measure to take the sum of MTD Sales (+) at the site ID level and then take the absolute value of the result.

MTD Sales (+) Business Level = ABS(SUMX(Account, [MTD Sales (+)]))

This measure will sum the MTD Sales (+) values for each Site ID and then take the absolute value of the result. When you use this measure in your Business level table, it should show the correct totals.

Let me know if you may require further guidance.

[cho]

Thank you @Sahir_Maharaj. Unforetunately this gives me a huge value which is incorrect. I changed your equation to below and tried as well. Then it gives a lower value.

MTD Sales (+) Business Level = ABS(SUMX(Account, [MTD Sales]))

But your example SUMX() gave me another idea. Is it possible to use ameasure based on SUMX() at the beginning without MTD Sales or MTD Sales (+)? So we do not need many measures.