Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JordanPearson
Helper II
Helper II

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

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

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:

BA_Pete_0-1606494665271.png

 

Or produce a list of accounts that meet your Contract criteria like this:

BA_Pete_0-1606495021932.png

 

Or create a measure to count the number of accounts that meet your Contract criteria like this:

 

_noofAccounts = DISTINCTCOUNT(JPearsonTable[Account ID:])

 

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

1 REPLY 1
BA_Pete
Super User
Super User

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:

BA_Pete_0-1606494665271.png

 

Or produce a list of accounts that meet your Contract criteria like this:

BA_Pete_0-1606495021932.png

 

Or create a measure to count the number of accounts that meet your Contract criteria like this:

 

_noofAccounts = DISTINCTCOUNT(JPearsonTable[Account ID:])

 

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.