Forum Discussion
Solved
Create a Measure/Solution aggreagteing Data and comparing 2 columns
Hello, I'm working with a dataset which is basically structured like this: The columns Customer_1 and Customer_2 refer to the same Customer. What I would like to do is create a Matrix Visu...
Hi AC123 -you don’t need to create a new table or model relationship if you just want to align Customer_1 with aggregated values from Customer_2.
create a measure with below filter condition as follow:
Total Transactions_MatchedToCustomer1 =
CALCULATE(
SUM(Data[Transactions_Customer_2]),
FILTER(
ALL(Data),
Data[Customer_2] = MAX(Data[Customer_1])
)
)This works please check.
danextian
Super User
Super UserHi AC123
You will need unpivot and then pivot your data.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYggyVIrViVZyArKM0MScgSxjNDEXIMsETcwVyDJFE4OwHaGmGiGJOUFNQBYD2WSGJgayyQJNDGSToQFEMBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer_1 = _t, Sales_Customer_1 = _t, Customer_2 = _t, Transactions_Customer_2 = _t, #"Entry Type" = _t]),
#"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Entry Type", "Index"}, "Attribute", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each [Value] <> null and [Value] <> ""),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Filtered Rows", {{"Attribute", each Text.BeforeDelimiter(_, "_"), type text}}),
#"Pivoted Column" = Table.Pivot(#"Extracted Text Before Delimiter", List.Distinct(#"Extracted Text Before Delimiter"[Attribute]), "Attribute", "Value"),
#"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column",{{"Sales", Int64.Type}, {"Transactions", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Index"})
in
#"Removed Columns"
Please see the attached pbix.