Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi, @FCF
You may try the following calculated column or measure.
Calculated column:
Column =
Targets[ Target Margin(CDN$) ]-
CALCULATE(
SUM(Sales[act_mar]),
FILTER(
ALL(Sales),
[Week]=EARLIER(Targets[Wk #])
)
)
Measure:
Measure =
SELECTEDVALUE(Targets[ Target Margin(CDN$) ])-
CALCULATE(
SUM(Sales[act_mar]),
FILTER(
ALL(Sales),
[Week]=SELECTEDVALUE(Targets[Wk #])
)
)
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If you are creating a measure, you cannot refer to a column this way. The measure returns a single value, so you must use one of the aggregate functions (SUM, MIX, MAX etc.).
If you want to compute something at row level then add a calculated column - then you can do it just like you do now.
_______________
If I helped, please accept the solution and give kudos! 😀
hi
thanks for the response, i am really new to this and am not sure what you mean by creating a calculated column
If you have a table and you want to add an additional column that calculates something based on other columns in this table, then you click calculated New Column and you create a formula to calculate.
You can read more about it here: https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/
_______________
If I helped, please accept the solution and give kudos! 😀
Hi again
the values that i want to work with come from 2 differnet tables.
i have created the calculated column, but am not able to see one of the columns form a differnt table.
this should not be so confusing.
thanks
@FCF , screenshot is not making clear, column, or measure. Both ways it is looking wrong. No need for the table name first. If measure the second one needs to aggeration.
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
this is an example of the targets.xlxs table
Salesperson | Customer | Wk # | Reference | Target Sales (CDN$) | Target Margin(CDN$) | YR |
JIM | VAPLWE | 2 | JIMVAPLWE2 | 615 | 145.38 | 2020 |
JIM | VAPLWE | 4 | JIMVAPLWE4 | 615 | 139.79 | 2020 |
JIM | VAPLWE | 6 | JIMVAPLWE6 | 630 | 266.2 | 2020 |
JIM | VAPLWE | 7 | JIMVAPLWE7 | 171 | 35.51 | 2020 |
JIM | VAPLWE | 8 | JIMVAPLWE8 | 652 | 181.59 | 2020 |
JIM | VAPLWE | 10 | JIMVAPLWE10 | 615 | 145.38 | 2020 |
JIM | VAPLWE | 12 | JIMVAPLWE12 | 615 | 156.58 | 2020 |
JIM | VAPLWE | 14 | JIMVAPLWE14 | 615 | 117.4 | 2020 |
JIM | VAPLWE | 16 | JIMVAPLWE16 | 696 | 141.73 | 2020 |
JIM | VAPLWE | 18 | JIMVAPLWE18 | 619 | 137.25 | 2020 |
JIM | VAPLWE | 20 | JIMVAPLWE20 | 619 | 148.45 | 2020 |
JIM | VAPLWE | 22 | JIMVAPLWE22 | 761 | 181.84 | 2020 |
JIM | VAPLWE | 24 | JIMVAPLWE24 | 622 | 152.02 | 2020 |
JIM | VAPLWE | 26 | JIMVAPLWE26 | 622 | 124.04 | 2020 |
JIM | VAPLWE | 28 | JIMVAPLWE28 | 624 | 153.74 | 2020 |
JIM | VAPLWE | 30 | JIMVAPLWE30 | 711 | 193.03 | 2020 |
JIM | VAPLWE | 32 | JIMVAPLWE32 | 608 | 137.87 | 2020 |
JIM | VAPLWE | 34 | JIMVAPLWE34 | 644 | 173.63 | 2020 |
JIM | VAPLWE | 36 | JIMVAPLWE36 | 743 | 158.37 | 2020 |
JIM | VAPLWE | 38 | JIMVAPLWE38 | 603 | 127.63 | 2020 |
JIM | VAPLWE | 41 | JIMVAPLWE41 | 781 | 177.06 | 2020 |
JIM | VAPLWE | 42 | JIMVAPLWE42 | 611 | 141.11 | 2020 |
JIM | VAPLWE | 44 | JIMVAPLWE44 | 697 | 154.79 | 2020 |
JIM | VAPLWE | 46 | JIMVAPLWE46 | 618 | 137.18 | 2020 |
JIM | VAPLWE | 52 | JIMVAPLWE52 | 604 | 139.52 | 2020 |
sales table
Customer | Posting date | Sales (CDN$) | ActualMargin | Salesperson | Week | Year | Sell-to customer no | Key3 | act_mar |
INPRPI | 28/12/2016 | 31.5 | 4.5 | JIM | 52 | 2016 | INPRPI | INPRPIJIM201652 | 4.5 |
INPRPI | 28/12/2016 | 31.5 | 4.5 | JIM | 52 | 2016 | INPRPI | INPRPIJIM201652 | 4.5 |
INPRPI | 28/12/2016 | 31.5 | 4.5 | JIM | 52 | 2016 | INPRPI | INPRPIJIM201652 | 4.5 |
INPRPI | 21/12/2016 | 31.5 | 4.5 | JIM | 51 | 2016 | INPRPI | INPRPIJIM201651 | 4.5 |
INPRPI | 20/12/2016 | 31.5 | 4.5 | JIM | 51 | 2016 | INPRPI | INPRPIJIM201651 | 4.5 |
INPRPI | 07/12/2016 | 31.5 | 4.5 | JIM | 49 | 2016 | INPRPI | INPRPIJIM201649 | 4.5 |
INPRPI | 05/12/2016 | 31.5 | 4.5 | JIM | 49 | 2016 | INPRPI | INPRPIJIM201649 | 4.5 |
INPRPI | 05/12/2016 | 31.5 | 4.5 | JIM | 49 | 2016 | INPRPI | INPRPIJIM201649 | 4.5 |
INPRPI | 05/12/2016 | 31.5 | 4.5 | JIM | 49 | 2016 | INPRPI | INPRPIJIM201649 | 4.5 |
INPRPI | 30/11/2016 | 31.5 | 4.5 | JIM | 48 | 2016 | INPRPI | INPRPIJIM201648 | 4.5 |
INPRPI | 30/11/2016 | 31.5 | 4.5 | JIM | 48 | 2016 | INPRPI | INPRPIJIM201648 | 4.5 |
INPRPI | 30/11/2016 | 31.5 | 4.5 | JIM | 48 | 2016 | INPRPI | INPRPIJIM201648 | 4.5 |
INPRPI | 05/10/2016 | 31.5 | 4.5 | JIM | 40 | 2016 | INPRPI | INPRPIJIM201640 | 4.5 |
3rd table
calendar
Date | M-# | Yr | Fiscal | Quarter | M-Name | Wk | Month | WK# | order | ord1 | ord2 | wk_sort |
28/07/19 | 7 | 2019 | FY20 | Q1-20 | Jul | 31.19 | 7~Jul | 31.19 | 1.00 | 1.20 | 1.200 | 31.191 |
29/07/19 | 7 | 2019 | FY20 | Q1-20 | Jul | 31.19 | 7~Jul | 31.19 | 1.00 | 1.20 | 1.201 | 31.192 |
30/07/19 | 7 | 2019 | FY20 | Q1-20 | Jul | 31.19 | 7~Jul | 31.19 | 1.00 | 1.20 | 1.202 | 31.193 |
31/07/19 | 7 | 2019 | FY20 | Q1-20 | Jul | 31.19 | 7~Jul | 31.19 | 1.00 | 1.20 | 1.203 | 31.194 |
01/08/19 | 8 | 2019 | FY20 | Q1-20 | Aug | 31.19 | 8~Aug | 31.19 | 1.00 | 1.20 | 1.204 | 31.195 |
02/08/19 | 8 | 2019 | FY20 | Q1-20 | Aug | 31.19 | 8~Aug | 31.19 | 1.00 | 1.20 | 1.205 | 31.196 |
i have used the calendar to create the relationships
from the target tabel i want to pull the [Target Margin(CDN$) ] -sales table [act_mar] = [margin_Variance]
i hope this helps.
Hi, @FCF
You may try the following calculated column or measure.
Calculated column:
Column =
Targets[ Target Margin(CDN$) ]-
CALCULATE(
SUM(Sales[act_mar]),
FILTER(
ALL(Sales),
[Week]=EARLIER(Targets[Wk #])
)
)
Measure:
Measure =
SELECTEDVALUE(Targets[ Target Margin(CDN$) ])-
CALCULATE(
SUM(Sales[act_mar]),
FILTER(
ALL(Sales),
[Week]=SELECTEDVALUE(Targets[Wk #])
)
)
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi
thanks for the help with this problem, i will test the two options.
Hi
I have found a way to make the measure work for me thanks for your help.
good morning
thanks for the response,
i have tried the measure but the reference to [week] is not working. i have an error message
"column week cannot be found or may not be used in this expression."
is it possible to upload the pbx file here?
Can you provide details of what you want to do, e.g. adding sample data?
To get data from another table, you must use the RELATED function.
_______________
If I helped, please accept the solution and give kudos! 😀
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |