Forum Discussion

JordanPearson's avatar
JordanPearson
Icon for Helper II rankHelper II
5 years ago
Solved

Building a Matrix to split out customers with multiple contracts purchased

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 ContractsContract AContract BContract C
11 1 
2211 
33111

 

Outcome:

 Contract AContract BContract CContract D
1 Contract (A + ...11  
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 + ... 11 
3 Contracts (B + ...1 1 
3 Contracts (C + ...11  
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! 🙂 

Need Help
  • BA_Pete's avatar
    BA_Pete
    5 years ago

    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

1 Reply

  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    5 years ago

    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