Building a Matrix to split out customers with multiple contracts purchased

[JordanPearson]

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! 🙂 

[BA_Pete · Accepted 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