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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mecr123
Frequent Visitor

Compare data rows so that total calculated is difference of the row in value and in percentage

hi

 

I am comparing 2 years of data

 

I have a matrix and have months across the Top and years down the side.  The matrix as standard will total each column.

 

How can make the matrix like the sample attached where it subtracts one value from another and displays difference and also the difference percentage.

 

 JanFebMarAprMayJunJulAugSepOctNovDec
2018120013001450180019001500120020002100220025002800
2019130013501560170017501400110022002200250026003500
Diff10050110-100-150-100-100200100300100700
% Diff8%4%8%-6%-8%-7%-8%10%5%14%4%

25%

 

 

regards

 

Michael

 

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @mecr123 

Open Edit queries,

Create a new blank query, write code as below in Advanced editor

8.png

let
    Source = Table1,
//add columns
    #"Inserted Year" = Table.AddColumn(Source, "Year", each Date.Year([date]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([date]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Text.Start(Date.MonthName([date]),3), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Month Name",{"date"}),
//filter table
    #"2017 TABLE"=Table.SelectRows(#"Removed Columns", each ([Year] = 2017)),
    #"2018 TABLE"=Table.SelectRows(#"Removed Columns", each ([Year] = 2018)),
//merge queries
    #"Merged Queries" = Table.NestedJoin(#"2017 TABLE", {"Month Name"}, #"2018 TABLE", {"Month Name"}, "new", JoinKind.LeftOuter),
    #"Expanded new" = Table.ExpandTableColumn(#"Merged Queries", "new", {"value"}, {"new.value"}),
//Add diff
    #"Added Custom" = Table.AddColumn(#"Expanded new", "Diff", each [new.value]-[value]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Diff%", each ([new.value]-[value])/[value]),
//diff table
    #"diff table"=Table.SelectColumns(#"Added Custom1",{"Year", "Month","Month Name", "Diff"}),
    #"Renamed Columns1" = Table.RenameColumns(#"diff table",{{"Diff", "value"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Year", type text}}),
    #"Replaced Value1" = Table.ReplaceValue(#"Changed Type1","2017","Diff",Replacer.ReplaceText,{"Year"}),
//diff% table
    #"diff% table"=Table.SelectColumns(#"Added Custom1",{"Year", "Month","Month Name", "Diff%"}),
    #"Renamed Columns2" = Table.RenameColumns(#"diff% table",{{"Diff%", "value"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Year", type text}}),
    #"Replaced Value2" = Table.ReplaceValue(#"Changed Type2","2017","Diff%",Replacer.ReplaceText,{"Year"}),
//reorder columns
    #"reorder1"=Table.ReorderColumns(#"2017 TABLE",{"Year", "Month", "Month Name", "value"}),
    #"reorder2"=Table.ReorderColumns(#"2018 TABLE",{"Year", "Month", "Month Name", "value"}),
    #"reorder3"=Table.ReorderColumns(#"Replaced Value1",{"Year", "Month", "Month Name", "value"}),
    #"reorder4"=Table.ReorderColumns(#"Replaced Value2",{"Year", "Month", "Month Name", "value"}),
//append queries
    #"Appended Query" = Table.Combine({#"reorder1", #"reorder2", #"reorder3",#"reorder4"})
in
#"Appended Query"

 

Close&&apply

Create a measure in “Query1” table

Measure = IF(MAX(Query1[Year])="diff%",FORMAT(MAX(Query1[value]),"0.00%"),MAX(Query1[value]))

9.png

Sort “month name” column by “month” column

10.png

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-juanli-msft
Community Support
Community Support

Hi @mecr123 

Open Edit queries,

Create a new blank query, write code as below in Advanced editor

8.png

let
    Source = Table1,
//add columns
    #"Inserted Year" = Table.AddColumn(Source, "Year", each Date.Year([date]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([date]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Text.Start(Date.MonthName([date]),3), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Month Name",{"date"}),
//filter table
    #"2017 TABLE"=Table.SelectRows(#"Removed Columns", each ([Year] = 2017)),
    #"2018 TABLE"=Table.SelectRows(#"Removed Columns", each ([Year] = 2018)),
//merge queries
    #"Merged Queries" = Table.NestedJoin(#"2017 TABLE", {"Month Name"}, #"2018 TABLE", {"Month Name"}, "new", JoinKind.LeftOuter),
    #"Expanded new" = Table.ExpandTableColumn(#"Merged Queries", "new", {"value"}, {"new.value"}),
//Add diff
    #"Added Custom" = Table.AddColumn(#"Expanded new", "Diff", each [new.value]-[value]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Diff%", each ([new.value]-[value])/[value]),
//diff table
    #"diff table"=Table.SelectColumns(#"Added Custom1",{"Year", "Month","Month Name", "Diff"}),
    #"Renamed Columns1" = Table.RenameColumns(#"diff table",{{"Diff", "value"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Year", type text}}),
    #"Replaced Value1" = Table.ReplaceValue(#"Changed Type1","2017","Diff",Replacer.ReplaceText,{"Year"}),
//diff% table
    #"diff% table"=Table.SelectColumns(#"Added Custom1",{"Year", "Month","Month Name", "Diff%"}),
    #"Renamed Columns2" = Table.RenameColumns(#"diff% table",{{"Diff%", "value"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Year", type text}}),
    #"Replaced Value2" = Table.ReplaceValue(#"Changed Type2","2017","Diff%",Replacer.ReplaceText,{"Year"}),
//reorder columns
    #"reorder1"=Table.ReorderColumns(#"2017 TABLE",{"Year", "Month", "Month Name", "value"}),
    #"reorder2"=Table.ReorderColumns(#"2018 TABLE",{"Year", "Month", "Month Name", "value"}),
    #"reorder3"=Table.ReorderColumns(#"Replaced Value1",{"Year", "Month", "Month Name", "value"}),
    #"reorder4"=Table.ReorderColumns(#"Replaced Value2",{"Year", "Month", "Month Name", "value"}),
//append queries
    #"Appended Query" = Table.Combine({#"reorder1", #"reorder2", #"reorder3",#"reorder4"})
in
#"Appended Query"

 

Close&&apply

Create a measure in “Query1” table

Measure = IF(MAX(Query1[Year])="diff%",FORMAT(MAX(Query1[value]),"0.00%"),MAX(Query1[value]))

9.png

Sort “month name” column by “month” column

10.png

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Will the formula HASONEVALUE be able to help you?

Hi Nij

thanks but I don't see how the formula HASONEVALUE will help ? what do yo have in mind ?

 

thanks

Michael

 

v-juanli-msft
Community Support
Community Support

Hi @mecr123 

This would need some transformation for the data model.

 

Could you clear me if you table has only year 2018, 2019, or many years?

If there are many years in your table,

Do you want to display every two years and their difference by slicer?

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Maggie

 

Yes I am only only going to load 2 years - 2018 & 2019. 

 

Ideally I would like to display it as I have outlioned as I want to see all months and the comparisons.

 

thanks for your help

 

regards

Michael

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors