Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Good afternoon all,
I am looking to create a Matrix view of clientbase, breaking it down into the number + type of contract each customer has purchased.
After spending a considerable amount of time on this, I'm truly stuck on how the dataset should be structured to achieve the Matrix view that I am looking for.
Dataset:
Account ID: | Count of Contracts | Contract A | Contract B | Contract C |
1 | 1 | 1 | ||
2 | 2 | 1 | 1 | |
3 | 3 | 1 | 1 | 1 |
Outcome:
Contract A | Contract B | Contract C | Contract D | |
1 Contract (A + ... | 1 | 1 | ||
1 Contract (B + ... | ||||
1 Contract (C + ... | ||||
1 Contract (D + ... | ||||
2 Contracts (A + ... | 1 | |||
2 Contracts (B + ... | 1 | |||
2 Contracts (C + ... | ||||
2 Contracts (D + ... | ||||
3 Contracts (A + ... | 1 | 1 | ||
3 Contracts (B + ... | 1 | 1 | ||
3 Contracts (C + ... | 1 | 1 | ||
3 Contracts (D + ... |
|
Essentially, the idea is for us to be able to see "This number of customers have 2 contracts, A + B", "This number of customers have 3 contracts, A+B+C" etc.
After many hours, I'm able to populate the Matrix to show that Client 2 has contracts A + B, populating the table with a (1) on the A + B, but I'm unable to also get a 1 in the opposite column B + A
Any assistance with this would be really appeciated! 🙂
Solved! Go to Solution.
Hi @JordanPearson ,
In Power Query, unpivot your dataset as follows. In Power Query, go to New Source>Blank Query then in Advanced Editor paste my code over the default code. You can then follow the steps I took to complete this.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWMFOB2rE61kBGQZQUUQosZAljGSqKFSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account ID:" = _t, #"Count of Contracts" = _t, #"Contract A" = _t, #"Contract B" = _t, #"Contract C" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Account ID:", type text}, {"Count of Contracts", Int64.Type}, {"Contract A", Int64.Type}, {"Contract B", Int64.Type}, {"Contract C", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Count of Contracts"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Account ID:"}, "Attribute", "Value")
in
#"Unpivoted Columns"
You can then set up a matrix like this:
Or produce a list of accounts that meet your Contract criteria like this:
Or create a measure to count the number of accounts that meet your Contract criteria like this:
_noofAccounts = DISTINCTCOUNT(JPearsonTable[Account ID:])
Pete
Proud to be a Datanaut!
Hi @JordanPearson ,
In Power Query, unpivot your dataset as follows. In Power Query, go to New Source>Blank Query then in Advanced Editor paste my code over the default code. You can then follow the steps I took to complete this.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAWMFOB2rE61kBGQZQUUQosZAljGSqKFSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account ID:" = _t, #"Count of Contracts" = _t, #"Contract A" = _t, #"Contract B" = _t, #"Contract C" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Account ID:", type text}, {"Count of Contracts", Int64.Type}, {"Contract A", Int64.Type}, {"Contract B", Int64.Type}, {"Contract C", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Count of Contracts"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Account ID:"}, "Attribute", "Value")
in
#"Unpivoted Columns"
You can then set up a matrix like this:
Or produce a list of accounts that meet your Contract criteria like this:
Or create a measure to count the number of accounts that meet your Contract criteria like this:
_noofAccounts = DISTINCTCOUNT(JPearsonTable[Account ID:])
Pete
Proud to be a Datanaut!
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |