Forum Discussion
How to Subtract Data From Same Column In Imported Table When Put In Matrix?
Hello Everyone,
I am trying to subtract data from 2 different years. The years from my imported table from the same column so in order for me to separate the years in PowerBI, the data is in a Matrix.
I want to create a column next to 2021 that subtracts the 2 years from each other to find the difference. How to I do this when the data is in the same column in my imported table?
Thank you in advance!
Hi sv98917n
You may achieve your goal by Power Query Editor or build a measure.
Your data model may like below.
Measure:
Measure = Var _2021 = CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Year]= MAX('Table'[Year])&& 'Table'[Date Alias]=MAX('Table'[Date Alias]))) Var _2020 = CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Year]= MAX('Table'[Year])-1&& 'Table'[Date Alias]=MAX('Table'[Date Alias]))) Return IF(SUM('Table'[Year])=2021,_2021-_2020,BLANK())However, this result may looks not very well.(It will show measure result for each year, we just need the result right beside 2021)
So you may do some transform in Power Query Editor.
Duplicate Table, and pivote the Year Column and add a custom column.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlDSUfIqzak0BNKm5kqxOmBRQyRRQzNDmDBMsRFI2NQSXTVI2MjYQCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, #"Date Alias" = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date Alias", type text}, {"Value", Int64.Type}}), #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Year]), "Year", "Value"), #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Subtract", each [2021]-[2020]), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Subtract", Int64.Type}}) in #"Changed Type1"Result:
You can download the pbix file from this link: How to Subtract Data From Same Column In Imported Table When Put In Matrix?
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
sv98917n , You need hybrid display, refer blog from Greg and Paul
For this Year vs last year with a separate date/year table
This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])Hi sv98917n
You may achieve your goal by Power Query Editor or build a measure.
Your data model may like below.
Measure:
Measure = Var _2021 = CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Year]= MAX('Table'[Year])&& 'Table'[Date Alias]=MAX('Table'[Date Alias]))) Var _2020 = CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Year]= MAX('Table'[Year])-1&& 'Table'[Date Alias]=MAX('Table'[Date Alias]))) Return IF(SUM('Table'[Year])=2021,_2021-_2020,BLANK())However, this result may looks not very well.(It will show measure result for each year, we just need the result right beside 2021)
So you may do some transform in Power Query Editor.
Duplicate Table, and pivote the Year Column and add a custom column.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMlDSUfIqzak0BNKm5kqxOmBRQyRRQzNDmDBMsRFI2NQSXTVI2MjYQCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, #"Date Alias" = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date Alias", type text}, {"Value", Int64.Type}}), #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Year]), "Year", "Value"), #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Subtract", each [2021]-[2020]), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Subtract", Int64.Type}}) in #"Changed Type1"Result:
You can download the pbix file from this link: How to Subtract Data From Same Column In Imported Table When Put In Matrix?
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
