Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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] )
@Anonymous
Total Sales Conditional =
IF (
SELECTEDVALUE ( Sales_before_mnth[Product Name] ) = "A",
SUM ( Sales_before_mnth[Value] ) + SUM ( Sales_current_mnth[Value] ),
0
)
@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
)
)
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.
@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
)
)
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?
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
)
)
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:
@Anonymous
Total Sales Conditional =
IF (
SELECTEDVALUE ( Sales_before_mnth[Product Name] ) = "A",
SUM ( Sales_before_mnth[Value] ) + SUM ( Sales_current_mnth[Value] ),
0
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
16 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |