Comparing stores

[MorningStar_92]

my data looks like this, from 2nd column onwards i have different stores like K071002,036235 etc, and in 1st column i have the categories having values for different stores. 
now here i want
1. To compare different stores based on their coupons gross revenue etc.
2. If suppose the % of coupons and discounts of k071002 is greater than 2.5% then it should flag it and then it should check it's gross revenue and then flag those stores too having gross revenue close to K071002's, so that it will help me in further analysing data.

Suggest me a method, of not let me know any other way in which i can do the same comparison, I'm open for suggestions.

[MorningStar_92]

Hey Dino, 
so Coupons & Discounts is basically sum of rows labelled as Coupon 1,2,3 and other discounts and gross revenue is sum of revenue 1,2,3,4 and digital revenue, 
and the % of Coupons will be calculated as coupons / gross revenue i.e; for store k071002 the % will be = (1764.78/218889.05)*100

i hope this explanation helps and will let u solve problem.

[v-junyant-msft · Accepted solution]

Hi @MorningStar_92 ,

Regarding it should check it's gross revenue and then flag those stores too having gross revenue close to K071002's, I'm not sure how you want to make the comparison specifically, so I directly calculated the difference between the other % of coupons and K071002's % of coupons.
Put all of the M function into the Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZBLC4MwEIT/SvCsIZuseVxbj4VCr9aDWGmFEouP/v4maquihQSG7MzHTtI0uJTv0vYlgSAMlAZmqNBOsuFm4Wzg7gU4ShTUeDOwGIFRQK9NrIymGlYJ8eN8WUl1r7r8SSaHT0oQMVXSSRELJUe2gwmKZsgc6+jQ5PZGTnWRd1Vt20Wc7a6Km4kT0xmR/ctzrj1jXJKkaou6t11Ld21jWcUp4yvmNPaP0vCxA6Brg15po1wFXDq3//FvERKRbe1N+tw9ymYOrefZBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Title = _t, K071002 = _t, #"036235" = _t, #"040699" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Title", type text}, {"K071002", type number}, {"036235", type number}, {"040699", type number}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type any}, {"Column3", type number}, {"Column4", type number}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"Title", type text}, {"Revenue 1", type number}, {"Revenue 2", type number}, {"Revenue 3", Int64.Type}, {"Digital Revenue", type number}, {"Co-Brand Locations Revenue", Int64.Type}, {"Revenue 4", Int64.Type}, {"", type any}, {"Coupons & Discounts.", type any}, {"Coupons1", type number}, {"Coupons2", type number}, {"Coupons3", Int64.Type}, {"Coupons & Discounts - Co-Brand Locations", Int64.Type}, {"Other Discounts", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "Gross Revenue", each [Revenue 1] + [Revenue 2] + [Revenue 3] +[Digital Revenue] + [#"Co-Brand Locations Revenue"] + [Revenue 4]),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Title", "Revenue 1", "Revenue 2", "Revenue 3", "Digital Revenue", "Co-Brand Locations Revenue", "Revenue 4", "Gross Revenue", "", "Coupons & Discounts.", "Coupons1", "Coupons2", "Coupons3", "Coupons & Discounts - Co-Brand Locations", "Other Discounts"}),
    #"Added Custom1" = Table.AddColumn(#"Reordered Columns", "Coupons & Discounts", each [Coupons1] + [Coupons2] + [Coupons3] + [#"Coupons & Discounts - Co-Brand Locations"] + [Other Discounts]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "% of Coupons", each [#"Coupons & Discounts"] / [Gross Revenue]),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "flag and difference", each let
    K071002_Coupons = Table.SelectRows(#"Added Custom2", each [Title] = "K071002"),
    K071002_Value = K071002_Coupons[#"% of Coupons"]{0},
    CustomColumn = Table.AddColumn(#"Added Custom2", "Result", each if [Title] = "K071002" and [#"% of Coupons"] >= 0.025 then "greater than 2.5%" 
    else [#"% of Coupons"] - K071002_Value)
    in
    CustomColumn),
    Custom = #"Added Custom3"{0}[flag and difference],
    #"Demoted Headers1" = Table.DemoteHeaders(Custom),
    #"Changed Type3" = Table.TransformColumnTypes(#"Demoted Headers1",{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type text}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}}),
    #"Transposed Table1" = Table.Transpose(#"Changed Type3"),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true])
in
    #"Promoted Headers1"

And the final output is as below:

And I change some data:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-junyant-msft]

Hi @MorningStar_92 ,

I'm sorry if I'm completely missing the point of what you're trying to accomplish here😢.
May I ask which rows of data you are referring to when you say coupons gross revenue? And how is the % of coupons and discounts of k071002 calculated?

It would be great if you could provide your desired results!

Best Regards,
Dino Tao