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
bryanrendra
Helper II
Helper II

Getting Subtraction from 1 column with certain criterias

Hi everyone, I really need your help to create a new substraction column in power query

editor. I am new to power bi and never use M language before.power bi1.jpg

This is the the photo of the data that i have. I want to get the price differences between two same book but different year. lets say " price difference between "History2 2013" and " History2 2014" is $8. 

 

For the book that does not have previous version of it the price difference should be 0.

 

 

 

 

 

Here is my disired result:

 

power bi 2.jpg 

The formula should look up every single row that meet certain condition such as same book and genre, but (Year-1)

 

Difference: [Price]-[Price from previous year from the same item]

 

Thank you so much!!

3 ACCEPTED SOLUTIONS
AllisonKennedy
Super User
Super User

Welcome to Power BI! There's a few ways you could do this. Is there a reason your result needs to be in a calculated column? I might recommend using a measure for this and using some DAX Time Intelligence features.

If you want it in Calculated Column, does it need to be done in Power Query, or would it be acceptable in DAX? (Depends what you need it for).

You could try as a New COLUMNS in Power BI or in Power Pivot Data model if working in Excel (not in Power Query Editor):
Previous Year Price = MAXX(FILTER(Table, EARLIER(Table[Year])<Table[Year] && Table[Genre] = EARLIER(Table[Genre])), Table[Price])
Price Difference = IF(ISBLANK(Table[Previous Year Price]), 0, Table[Price] - Table[Previous Year Price])

Note the calculation for Previous Year Price assumes the price is always increasing, so takes the maximum price from all previous years.

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

Anonymous
Not applicable

######EDITED############

 

try using this code power query:

 

 

yourTab = Source{[Item="Foglio1",Kind="Sheet"]}[Data],
breakTab=(tab as table) =>
let
 tabSorted=Table.Sort(tab,{"Price"}),
 breakList=List.Accumulate(List.Numbers(1,Table.RowCount(tabSorted)-1), {},(s,c)=>s&{tabSorted[Price]{c}-tabSorted[Price]{c-1}}) 

breakTab=(tab as table) =>
let
 tabSorted=Table.Sort(tab,{"Year"}),
 breakList=List.Accumulate(List.Numbers(0,Table.RowCount(tabSorted)), {},(s,c)=>s&{try (tabSorted[Price]{c}-tabSorted[Price]{c-1}) otherwise 0}) 
in
Table.FromColumns({tab[Year],tab[Price],breakList},{"Year","Price","difference"}),
    #"Promoted Headers" = Table.PromoteHeaders(yourTab, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Book", type text}, {"Genre", type text}, {"Year", Int64.Type}, {"Price", Int64.Type}, {"Difference", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Book", "Genre"}, {{"diff", each breakTab(_)}}),
    #"Expanded diff" = Table.ExpandTableColumn(#"Grouped Rows", "diff", {"Year", "Price", "difference"}, {"Year", "Price", "difference"})
in
    #"Expanded diff"

 

 

could get somethoink like this:

 

 

image.png

View solution in original post

Try this formula with ALL() and CALCULATE:

PreviousPrice =
VAR _CurrentYear = MAX(BookPrices[Year])
VAR _PreviousYear = CALCULATE(MAX(BookPrices[Year]), ALL(BookPrices[Year]), BookPrices[Year]<_CurrentYear)
RETURN
CALCULATE(SELECTEDVALUE(BookPrices[Price]), BookPrices[Year]=_PreviousYear)

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

View solution in original post

18 REPLIES 18
AllisonKennedy
Super User
Super User

Welcome to Power BI! There's a few ways you could do this. Is there a reason your result needs to be in a calculated column? I might recommend using a measure for this and using some DAX Time Intelligence features.

If you want it in Calculated Column, does it need to be done in Power Query, or would it be acceptable in DAX? (Depends what you need it for).

You could try as a New COLUMNS in Power BI or in Power Pivot Data model if working in Excel (not in Power Query Editor):
Previous Year Price = MAXX(FILTER(Table, EARLIER(Table[Year])<Table[Year] && Table[Genre] = EARLIER(Table[Genre])), Table[Price])
Price Difference = IF(ISBLANK(Table[Previous Year Price]), 0, Table[Price] - Table[Previous Year Price])

Note the calculation for Previous Year Price assumes the price is always increasing, so takes the maximum price from all previous years.

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Hi, can you please provide a solution on power editor as i have the same issue with big data on directquery mode. if possible to share the pbix file as well, it will be the best. thanks for your support.

I really need your suggestion with this problem, considering that i will run a big data set and i need this calculation take less memory and run fast when i pull up a new data set . whats your recommendation?

Anonymous
Not applicable

 

 

"considering that you will run a big data set and you need this calculation take less memory and run fast",

consider to try this version which could be more performing.

how many rows and column your data set has?

 

 

let
...
    #"Promoted Headers" = Table.PromoteHeaders(yourTab, [PromoteAllScalars=true]),
    #"Sorted Rows" = Table.Sort(#"Promoted Headers",{{"Book", Order.Ascending}, {"Genre", Order.Ascending}, {"Year", Order.Ascending}}),
    breakTab=(tab as table) =>
        let 
        diffPrice=List.Transform(List.Numbers(0,Table.RowCount(tab)), each try (tab[Price]{_}-tab[Price]{_-1}) otherwise 0) 
        in
    Table.FromColumns({tab[Year],tab[Price],diffPrice},{"Year","Price","difference"}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Book", "Genre"}, {{"diff", each breakTab(_)}},GroupKind.Local),
    #"Expanded diff" = Table.ExpandTableColumn(#"Grouped Rows", "diff", {"Year", "Price", "difference"}, {"Year", "Price", "difference"})
in
    #"Expanded diff"

 

 

Columns will calculate for all data model, Measures only calculate for what you ask for in visualization, so if your dataset is very large and you're only viewing filtered subsets of the data, the measure option should be faster, but it really depends on a lot of other factors as well.

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

It would be amazing if you could show me how to make a measure to solve that problem Thank you so much

@bryanrendra  I have updated this with ALL() option for complete solution. 

 

See if this makes sense, all as new MEASURES (you can mark it also as solution if it works).
Note: I have split them into smaller measures to make it easier to read and so that you can use the Previous and Latest Price measures on their own in case that is something you want to analyze:

LatestPrice = MAXX(FILTER(BookPrices, BookPrices[Year]=MAX(BookPrices[Year])),BookPrices[Price])

PreviousPrice =
VAR LatestYear = MAXX(BookPrices,BookPrices[Year])
RETURN MAXX(FILTER(BookPrices, BookPrices[Year]<LatestYear),BookPrices[Price])

-- This PreviousPrice works only when YEAR is not in visual or filter.

 

PreviousPrice =
VAR _CurrentYear = MAX(BookPrices[Year])
VAR _PreviousYear = CALCULATE(MAX(BookPrices[Year]), ALL(BookPrices[Year]), BookPrices[Year]<_CurrentYear)
RETURN
CALCULATE(SELECTEDVALUE(BookPrices[Price]), BookPrices[Year]=_PreviousYear)
-- This PreviousPrice uses the ALL() function to ignore filter on YEAR so works even when YEAR is included in visual or filter. If YEAR is not included in visual or filter, it will take the latest YEAR's data. 



PriceDifference = IF(ISBLANK([PreviousPrice]), 0, [LatestPrice]-[PreviousPrice])


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Hi Allison, i think its a incredible approach. However when i tried to calculate the previous price, it doesnt work. Do you mind to check the formula one more time ? i think we need to find the year right before. If its a python/ java might be easier. get_value_Table[price] where table[year]-1. something like that. your help is wonderful!!

You could do Year-1, but I wasn't sure if there was a price for every year or not, so went for latest year.
Try this updated measure:
PreviousPrice =
VAR LatestYear = MAXX(BookPrices,BookPrices[Year])
VAR _PreviousYear = MAXX(FILTER(BookPrices, BookPrices[Year]<LatestYear),BookPrices[Year])
RETURN MAXX(FILTER(BookPrices, BookPrices[Year]=_PreviousYear),BookPrices[Price])

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Here is the result what i got, and still get nothing. Considering also that there is a certain year that the book sold out so there will be no price / "0"

pbi3.jpg

Aha, since you've got Year in your table, you'll need to use an ALL() filter to get that to display properly, or remove Year from the table visual.

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

I dont want to remove the year table since its needed. do you mind to elaborate what you mean by using all()?

 

Try this formula with ALL() and CALCULATE:

PreviousPrice =
VAR _CurrentYear = MAX(BookPrices[Year])
VAR _PreviousYear = CALCULATE(MAX(BookPrices[Year]), ALL(BookPrices[Year]), BookPrices[Year]<_CurrentYear)
RETURN
CALCULATE(SELECTEDVALUE(BookPrices[Price]), BookPrices[Year]=_PreviousYear)

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

this is the answer that i have been looking for,

Thank you so much Allison, you have been really helpfull. I really appreciate it 🙂

Anonymous
Not applicable

######EDITED############

 

try using this code power query:

 

 

yourTab = Source{[Item="Foglio1",Kind="Sheet"]}[Data],
breakTab=(tab as table) =>
let
 tabSorted=Table.Sort(tab,{"Price"}),
 breakList=List.Accumulate(List.Numbers(1,Table.RowCount(tabSorted)-1), {},(s,c)=>s&{tabSorted[Price]{c}-tabSorted[Price]{c-1}}) 

breakTab=(tab as table) =>
let
 tabSorted=Table.Sort(tab,{"Year"}),
 breakList=List.Accumulate(List.Numbers(0,Table.RowCount(tabSorted)), {},(s,c)=>s&{try (tabSorted[Price]{c}-tabSorted[Price]{c-1}) otherwise 0}) 
in
Table.FromColumns({tab[Year],tab[Price],breakList},{"Year","Price","difference"}),
    #"Promoted Headers" = Table.PromoteHeaders(yourTab, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Book", type text}, {"Genre", type text}, {"Year", Int64.Type}, {"Price", Int64.Type}, {"Difference", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Book", "Genre"}, {{"diff", each breakTab(_)}}),
    #"Expanded diff" = Table.ExpandTableColumn(#"Grouped Rows", "diff", {"Year", "Price", "difference"}, {"Year", "Price", "difference"})
in
    #"Expanded diff"

 

 

could get somethoink like this:

 

 

image.png

Hi, can you please share the pbix file. thanks for your support.

Hi, i see trhis is can solve my issue as well but i am not expert in power bi so i appreciate your support to provide me by the pbix file so i can understand more what steps you did so i can follow. thanks for your support.

Hi, thank you so much for the alternative way that you have given 🙂 its helpful!!

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.

Top Solution Authors
Top Kudoed Authors