topic Re: Calculating an agregated sum and adding a value to the sum in DAX Commands and Tips

Calculating an agregated sum and adding a value to the sum

Anonymous — Tue, 29 Nov 2022 05:33:47 GMT

Hi, I have the following problem.

I have a list of sales in current month for different products (let's say A, B, C):

Table Name: Sales_current_mnth

Product name	QTY	Value
A	5	10
B	10	10
A	1	2
C	5	15
A	1	2
C	10	30
A	25	50
B	10	10

Additionally, I have an aggregated list of sales from previous months:

Table Name: Sales_before_mnth

Product name	QTY	Value
A	100	200
B	50	50
C	30	90

I need to calculate a measure:

1) calculeate the sales numbers from the current month

2) sum the calculated values and the aggrgated from the prevous months

3) return a table with QTYs and values by product name.

Is it possible to calculate in DAX?

Re: Calculating an agregated sum and adding a value to the sum

tamerj1 — Tue, 29 Nov 2022 08:44:01 GMT

Hi Anonymous
Please refer to attached sample file with the solution

Total QTY = SUM ( Sales_before_mnth[QTY] ) + SUM ( Sales_current_mnth[QTY] )

Total Sales = SUM ( Sales_before_mnth[Value] ) + SUM ( Sales_current_mnth[Value] )

Re: Calculating an agregated sum and adding a value to the sum

Anonymous — Thu, 01 Dec 2022 08:39:42 GMT

Thank you very much! Can you also advise, how I can create conditional measure? Let's say if Product Name = "A", then do calculation, else return 0. My 1st try was not successful:

Total Sales Conditional = IF(Sales_before_mnth[Product Name]="A", SUM ( Sales_before_mnth[Value] ) + SUM ( Sales_current_mnth[Value] ), 0)

Re: Calculating an agregated sum and adding a value to the sum

tamerj1 — Thu, 01 Dec 2022 09:04:08 GMT

Anonymous

Total Sales Conditional = IF ( SELECTEDVALUE ( Sales_before_mnth[Product Name] ) = "A", SUM ( Sales_before_mnth[Value] ) + SUM ( Sales_current_mnth[Value] ), 0 )

Re: Calculating an agregated sum and adding a value to the sum

Anonymous — Thu, 01 Dec 2022 09:07:38 GMT

Additionally, I have added your formula to my work data (which is much larger, then test data) and there this measure does not calculate sums over product name. The result looks like this (for example for product A):

Total Value (A) = Value current month(A) + Sum(Values before month)

It's obvious, I have a problem in data releationship, but I can't really track it.

Re: Calculating an agregated sum and adding a value to the sum

tamerj1 — Thu, 01 Dec 2022 09:18:41 GMT

Anonymous
Make sure the product names in both tables have exactly the same splilling. Adiitionally in order to have correct totals please try

Total Sales Conditional = SUMX ( VALUES ( Sales_before_mnth[Product Name] ), IF ( Sales_before_mnth[Product Name] = "A", CALCULATE ( SUM ( Sales_before_mnth[Value] ) + SUM ( Sales_current_mnth[Value] ) ), 0 ) )

Re: Calculating an agregated sum and adding a value to the sum

Anonymous — Thu, 01 Dec 2022 09:44:33 GMT

Hi, it looks that if there is a new product name is added to the current mnth table then the formula calculeats total of the sales_before_month, e.g. if there is a new product D added:

Product name	QTY	Value
A	5	10
B	10	10
A	1	2
C	5	15
A	1	2
C	10	30
A	25	50
B	10	10
D	5	100

then I get the total (formula does not find D in the Sales_before_mnth). So how can I handle the exception in this case?

Re: Calculating an agregated sum and adding a value to the sum

tamerj1 — Thu, 01 Dec 2022 10:47:18 GMT

Hi Anonymous

please try by activating bi-directional relationship and slice by Sales_current_mnth[Product Name] the formula would be

Total Sales Conditional =
SUMX (
VALUES ( Sales_current_mnth[Product Name] ),
IF (
Sales_current_mnth[Product Name] = "A",
CALCULATE (
SUM ( Sales_before_mnth[Value] ) + SUM ( Sales_current_mnth[Value] )
),
0
)
)