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.
Stuck trying to figure out how to insert a calculated column... below is my code but it's only returning the values that already exist in the column May16
Column = CALCULATE(SUM(Sheet1[May16]),FILTER(ALL(Sheet1[Day of Month]),Sheet1[Day of Month]<= MAX (Sheet1[Day of Month])))
Day of Month is just a column with the day of the month number so May 31st = 31
Solved! Go to Solution.
Running = SUMX(
FILTER( Sheet1,
Sheet1[Day Of Month]<=EARLIER(Sheet1[Day Of Month])
),
Sheet1[Column With Amount])
....didn't know the name of the column with the amount -- - so made that up....
if this doesn't work please copy/type in a brief sample of the table itself to be viewed....
Hi,
You can create a calculated column to do it. See my screenshots below.
Running = SUMX(FILTER( Sheet1, Sheet1[Day Of Month]<=EARLIER(Sheet1[Day Of Month])), Sheet1[Column With Amount])
Or you also can use a measure to achieve it.
Measure = CALCULATE(sum(Sheet1[Column With Amount]), FILTER(Sheet1, Sheet1[Day Of Month] <= MAX(Sheet1[Day Of Month])))
The difference between measure and calculated column is that a calculated column is just like any other column in a table and you can use it in rows, columns, filters, or values of a pivot table or any other report. While measure is an aggregated values from many rows in a table. The value of a calculated column is computed during data refresh and uses the current row as a context; A measure operates on aggregations of data defined by the current context.
For more details about calculated column and measure, you can see here.
BTW, for resolving “Sheet1” name issue, you may already have existing object named “Sheet1”. You should change it to another name.
Best Regards
Alex
Hi,
You can create a calculated column to do it. See my screenshots below.
Running = SUMX(FILTER( Sheet1, Sheet1[Day Of Month]<=EARLIER(Sheet1[Day Of Month])), Sheet1[Column With Amount])
Or you also can use a measure to achieve it.
Measure = CALCULATE(sum(Sheet1[Column With Amount]), FILTER(Sheet1, Sheet1[Day Of Month] <= MAX(Sheet1[Day Of Month])))
The difference between measure and calculated column is that a calculated column is just like any other column in a table and you can use it in rows, columns, filters, or values of a pivot table or any other report. While measure is an aggregated values from many rows in a table. The value of a calculated column is computed during data refresh and uses the current row as a context; A measure operates on aggregations of data defined by the current context.
For more details about calculated column and measure, you can see here.
BTW, for resolving “Sheet1” name issue, you may already have existing object named “Sheet1”. You should change it to another name.
Best Regards
Alex
This sounds suspiciously like something that should be done as a measure rather than a column. I'm not sure what sort of data structure would use a column for this.
Proud to be a Super User!
Very new to BI... what is exactly is the difference between a measure and a calculated column? I know that i Can create the line graph I need with this column in my data. The cumulative total is the number of complaints so I can graph trendlines for different months.
a Calculated Column gets added to the fundamental data model table
while a Measure gets calculated on the fly of a visual (in this case a table visual)
To use a Measure:
Running2 = CALCULATE(SUM(Sheet1[Column With Amount]),
FILTER( ALL(Sheet1),
Sheet1[Day Of Month]<=MAX(Sheet1[Day Of Month])
)
)
When significant row counts are involved there can be an impact on performance
Running = SUMX(
FILTER( Sheet1,
Sheet1[Day Of Month]<=EARLIER(Sheet1[Day Of Month])
),
Sheet1[Column With Amount])
....didn't know the name of the column with the amount -- - so made that up....
if this doesn't work please copy/type in a brief sample of the table itself to be viewed....
My table now looks like this as I'll need to only see the cumulative totals based on each month.
My future concern is what to do when it gets to today's date. In the current month I would want the cumulative total to stop after the last non 0 value. In this case September 8th would be NULL. Also, I did a find and replace on null with 0. I may need to put this back.
But I see in your pasted screen shot it has check mark and 'no syntax problem detected'....
your interface is different than mine - you are in Query Editor
try this - in Desktop, not query editor but just regular Data view (icon in left frame), select Table in Fields frame right, then in Modeling tab there is the 'New Column' choice.... and one offers the expression field just below the ribbon....
If run this code from the desktop I get Failed to resolve name Sheet1. This may be from my new table format.
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 |
---|---|
108 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |