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.
Apologies if this gets double posted. I have a chart with a trend line for last and current year. I need to get the value of that trend line at a couple of points.
I strated using the DAX Query behing my chart. That gives me the dax behind the trend calculated for two years.
This uses SUMMARIZECOLUMS and I just want to SUM the value Banana from the table it creates but I cant as it it not a base table.
Any Suggestions gratefully received.
Thanks
E
// DAX Query
DEFINE
VAR __DS0FilterTable =
TREATAS(
{"201906",
"201907",
"201908",
"201909",
"201910",
"201911",
"201912",
"202001",
"202002",
"202004",
"201905"},
'Date'[CY Month & Year Sort]
)
VAR __DS0Core =
SUMMARIZECOLUMNS(
'Date'[CY Month & Year Sort],
__DS0FilterTable,
"Banana", IF( MIN('Date'[CY Month & Year Sort]) = "201905" || MIN('Date'[CY Month & Year Sort]) = "201906", 'Measure Trend'[Linear Regression], 0),
"Sales", 'Measures Base'[Sales],
"Linear_Regression", 'Measure Trend'[Linear Regression]
)
VAR __DS0PrimaryWindowed =
TOPN(1001, __DS0Core, 'Date'[CY Month & Year Sort], 1)
EVALUATE
__DS0PrimaryWindowed
Awesome,
Thanks
Hi @ells
You can create a table variable in a measure and use the measure to return the SUM result of a column in this table variable. This may be doable.
Reference:
Regards,
Community Support Team _ Jing
And to get the sum of the values in a table variable?
The example there does not work for me. I get an error as I can not sum the values of a table that is not a base table?
Hi @ells
Sorry for the late reply. SUMX function is able to deal with a table variable. I modified your DAX codes into a measure and tested it with some data. It can accept the table variable and find the [Banana] column in it.
Total =
VAR __DS0FilterTable =
TREATAS(
{"202012",
"202101",
"202102",
"202103"},
'Table'[YearMonth]
)
VAR __DS0Core =
SUMMARIZECOLUMNS(
'Table'[YearMonth],
__DS0FilterTable,
"Banana", IF( MIN('Table'[YearMonth]) = "202101" || MIN('Table'[YearMonth]) = "202102", SUM('Table'[Budget]), 0)
)
VAR __DS0PrimaryWindowed =
TOPN(2, __DS0Core, 'Table'[YearMonth], 1)
RETURN
SUMX(__DS0PrimaryWindowed,[Banana])
I have attached the test PBIX file below. You can download it to see the details.
Regards,
Community Support Team _ Jing
@v-jingzhang
Same errror messages as befgore
The expression specified in the query is not a valid table expression
Only thing I can see different is I dont sum the value (Table[Budget]) as my value is a measure,
E
Ok,
I see the SUMX works. However I think this is falling down on the underlying measure, It looks like the trend line is not helping this calculation.
Thanks
E
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |