count of mode

[jboschee]

Hello,

I'm trying to count the number of times my Mode measure is equal to the prices in the row.  I want to duplicate the Mode Count column below.

Any help would be appreciated!

Product	Amazon	Walmart	Bestbuy	Home Depot	Lowes	Target	Mode Count
1	200	200	150	125	300	275	2
2	50	75	75	75	85	60	3
3	300	300	600	450	400	500	2
4	25	50	60	60	60	45	3
5	100	100	75	75	65	85	2

[Anonymous · Accepted solution]

Hi @jboschee 

You need to unpivot the column first, you can put the following code in Advanced Editor in power query

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY7BDcAwCAN34Z0HECDdBbH/Gg3QRsrnZGGDcQeCAYx4SFpk3Zw9XwoxHHjrMpdeeBKGlZlnq2lFqTUprdhJycv6n7Qb0o1pU3+Fd7F9xREv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Amazon = _t, Walmart = _t, Bestbuy = _t, #"Home Depot" = _t, Lowes = _t, Target = _t]),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", Int64.Type}, {"Amazon", Int64.Type}, {"Walmart", Int64.Type}, {"Bestbuy", Int64.Type}, {"Home Depot", Int64.Type}, {"Lowes", Int64.Type}, {"Target", Int64.Type}}),

    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Product"}, "Attribute", "Value")

in

    #"Unpivoted Other Columns"

Then apply it to table, and create a measure in the table

Mode = COUNTAX(FILTER(ALL('Table'),'Table'[Product]=SELECTEDVALUE('Table'[Product])&&'Table'[Value]=SELECTEDVALUE('Table'[Value])),[Value])

Output:

Best Regards,

^{Yolo Zhu}

^{If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.}