Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
zoubaier
Helper I
Helper I

Calculate based on previous rows - Language M

Hello,

 

I need your help to make  a  calculation based on previous rows and specific filters .

 

  cars
SA0
SA1
SA0
SA2
SA1
SA2
SB0
SB0
SB1
ZC0
ZC2

 

 

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. 

1 ACCEPTED SOLUTION
v-ljerr-msft
Employee
Employee

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.

t1.PNG

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] ) )

t2.PNG

 

Regards

View solution in original post

5 REPLIES 5
v-ljerr-msft
Employee
Employee

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.

t1.PNG

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] ) )

t2.PNG

 

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.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Hello @ImkeF,

 

Thanks for answering and trying to help.

The best solution i found is to to summary table (DAX Language) and do the following calculation based on it.

The group by option will modify the table source structure howerver i need it to keep the same structure to do other visualisations. This is one of the limit of Language M, hope in the future to find an option to do a group calculation or summary by in the query without changing the table structure.

 

Best Regards

Hello @zoubaier,

this option exists already. In mulitple ways actually:

 

1) If you have a table that you want to load as it is to the data model and want to create another table that is based on that, you just reference your first "big" query instead of further processing it (check the name of the query -> rightclick your mouse -> reference). This will create a new query that you can shape as you like.

 

2) If you ended up in a query that you have processed too far already, like in this case "destroyed all details by grouping", you can split this query into 2. Therefore you check the step that shall be the first in your new query -> rightclick your mouse -> "Extract Previous". This will open up a dialgog where you will be promted to type in a name for the "new" 1st query.

You'll find a nice presentation of this functionality here: https://www.youtube.com/watch?time_continue=796&v=2v7LUD7MJaw (start at 13:00)

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.