Forum Discussion
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.
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:
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!!
- 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. ######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:
- 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)
18 Replies
- 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.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)RETURNCALCULATE(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])
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?
- 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.
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.
