Advanced pivot? Advanced matrix view?

[fbrickley]

Hi everyone

I currently have data in the following format where each company can have multiple packages, sale currency and billing currency. Crucially the same company could within a particular month have multiple sale currencies, billing currencies and packages.

CompanyName	PackageName	SaleCurrency	SaleAmount	BillingCurrency	BillingAmount	BillingDate
Company 1	Package 1	EUR	101	GBP	123	01-03-22
Company 1	Package 2	USD	300	GBP	400	01-02-22
Company 1	Package 2	ZAR	400	GBP	300	01-02-22

 I would like to put it into the format, where each combination of CompanyName, SaleCurrency, BillingCurrency has its own row:

			2022
			Feb		Mar
CompanyName	SaleCurrency	BillingCurrency	SaleAmount	BillingAmount	SaleAmount	BillingAmount
Company 1	EUR	GBP			101	123
Company 1	USD	GBP	300	400
Company 1	ZAR	GBP	400	300

Where the year and month fields can be drilled down on. Essentially this is like a matrix but with some additional fields to summarise over held on the left hand side. I'd also like to be able to open up each row to see summaries for individual packages.

The data goes back several years and will continue to be added to month-by-month. Is this possible?

Currently I have the idea to create a unique ID for each combination of CompanyName, SaleCurrency and BillingCurrency, create a new table with those as the values in the first column - this I have managed. The next step would be to programatically create columns, one for each month, then fill in the required values. I really don't know where to start with this - any help is much appreciated!

Thanks

[fbrickley]

Thanks for your reply. Unfortunately your solution doesn't work when a company has multiple sale or billing currencies within the same month. I've made my requirements clearer to show that I would like these to have their own rows so that within each month it's possible to see what's happening for each company in each combination of currencies - is this possible?

[v-shex-msft]

Hi @fbrickley,

Perhaps you can consider moving the currency fields to row fields instead of column fields. Then power bi matrix visual will group your records based on company and currency fields.

Regards,

Xiaoxin Sheng

[PijushRoy]

Hi

Power BI Matrix Table

Change The alignment of the Column Header. If needed, I will share PBIX file, please let me know.

If solve the requirement, Please mark this answer as SOLUTION and give me Kedos.

Power Query Steps
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PLUjMq1QwVNJRCkhMzk5MTwWzXUODgKShAYjt7hQAYhsZA0kDQ10DY10jAyMjpVgd7NqNgOzQYBcgaWxgANduAmaDtBtBtccCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CompanyName = _t, PackageName = _t, SaleCurrency = _t, SaleAmount = _t, BillingCurrency = _t, BillingAmount = _t, BillingDate = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"CompanyName", type text}, {"PackageName", type text}, {"SaleCurrency", type text}, {"SaleAmount", Int64.Type}, {"BillingCurrency", type text}, {"BillingAmount", Int64.Type}, {"BillingDate", type date}}),
#"Inserted Month Name" = Table.AddColumn(#"Changed Type", "Month Name", each Date.MonthName([BillingDate]), type text),
#"Inserted Year" = Table.AddColumn(#"Inserted Month Name", "Year", each Date.Year([BillingDate]), Int64.Type),
#"Reordered Columns" = Table.ReorderColumns(#"Inserted Year",{"CompanyName", "PackageName", "SaleCurrency", "BillingCurrency", "SaleAmount", "BillingAmount", "BillingDate", "Month Name", "Year"})
in
#"Reordered Columns"