Forum Discussion
Calculate based on previous rows - Language M
Hello,
I need your help to make a calculation based on previous rows and specific filters .
| cars | ||
| S | A | 0 |
| S | A | 1 |
| S | A | 0 |
| S | A | 2 |
| S | A | 1 |
| S | A | 2 |
| S | B | 0 |
| S | B | 0 |
| S | B | 1 |
| Z | C | 0 |
| Z | C | 2 |
How many cars for A/B/C ? i need formulas using language M in order to use it in data view to return new numerical formulas to be used on future columns.
Hi zoubaier,
In this scenario, I would suggest you to use SUMMARIZE Function (DAX) to create a new calculate table in Data view. See my sample below.
I assume you have a table called "Table1" like below.
The you should be able to use the formula below to create a new table you need in Data view.
NewSummarizeTable = SUMMARIZE ( Table1, Table1[column2], "TotalCars", SUM ( Table1[cars] ) )
Regards
5 Replies
Hi zoubaier,
In this scenario, I would suggest you to use SUMMARIZE Function (DAX) to create a new calculate table in Data view. See my sample below.
I assume you have a table called "Table1" like below.
The you should be able to use the formula below to create a new table you need in Data view.
NewSummarizeTable = SUMMARIZE ( Table1, Table1[column2], "TotalCars", SUM ( Table1[cars] ) )
Regards
Thanks v-ljerr-msft for answering,
I don't prefer this option as i ll need to use the resulted sum in other calculation via query editor this why I prefer magic formulas on that .
I ll try working with it until finding another option.
Best Regards
On the Transform-tab top left you'll find the "Group By" button. Click that after you've checked a cell in the column you want to group by/aggregate.
You'll see a couple of options to create the aggregates, a SUM on the Cars-column will probably be what you want.
Just play around with it a bit and study the resulting codes.
