Group multiple values from same and different columns

[Mha35]

Hello!

I have a database that has all the sales from a date, per Product and Currency, by 2 different companies.

Data sample:

Date	Product	Currency	Company	Value
01/01/2025	A	USD	X	10
01/01/2025	A	BRL	X	10
01/01/2025	B	USD	X	10
01/01/2025	C	USD	Z	10
01/01/2025	C	BRL	X	10
02/01/2025	A	USD	X	10
02/01/2025	C	BRL	X	10
03/01/2025	A	USD	Z	10
03/01/2025	B	USD	X	10
03/01/2025	B	BRL	X	10
04/01/2025	A	USD	X	10
04/01/2025	C	USD	X	10

I wanted to put this in powerBI by having the date unique, then grouping by company, then currency and then product. After, I would sum all the values for that date and currency. This is what im trying to achive:

	Company X									Company Z
		USD				BRL					USD				BRL
Date	A	B	C	SUM	A	B	C	SUM		A	B	C	SUM	A	B	C	SUM
01/01/2025	10	10	0	20	10	0	10	20		0	0	10	10	0	0	0	0
02/01/2025	10	0	0	10	0	0	10	10		0	0	0	0	0	0	0	0
03/01/2025	0	10	0	10	0	0	0	0		10	0	0	10	0	0	0	0
04/01/2025	10	0	10	20	0	0	0	0		0	0	0	0	0	0	0	0

[v-dineshya]

Hi @Mha35 ,

Thanks for reaching out to the Microsoft fabric community forum. 

Please follow below steps:

Step 1: Load Your Data
In Power BI Desktop, load your sales data into Power BI. Go to Power Query Editor (Transform Data).

Step 2: Add a Missing Column for All Products :
Since your data has zero values for combinations that don't exist, you'll need to ensure all combinations exist (Date, Company, Currency, Product).

In Power Query:

Create reference tables for Date, Company, Currency, and Product. Use Remove Duplicates to create unique value lists.

Create a cross join to generate all combinations (e.g., with a custom step using Table.AddColumn). Merge this master combination table with your actual data to bring in values (with nulls for missing rows). Replace null in the Value column with 0.

Step 3: Add a Matrix visual.

Set up the fields like this:

Rows --->Date
Columns -->Company → Currency → Product
Values -->Sum of Value

Top level: Company

Under Company: Currency

Under Currency: Product

You can turn Subtotals on or off per level (like per Currency or per Company). Rename columns/headers using DAX or calculated columns if needed for cleaner display.

With the matrix set up, Power BI will automatically: Fill missing combinations with 0 (assuming you handled that in Power Query). Aggregate values as needed.

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[v-dineshya]

Hi @Mha35 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and consider giving a KUDOS. Feel free to reach out if you need further assistance.

Regards,

Dinesh

[v-dineshya]

Hi @Mha35 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and consider giving a KUDOS. Feel free to reach out if you need further assistance.

Regards,

Dinesh

[v-dineshya]

Hi @Mha35 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and consider giving a KUDOS. Feel free to reach out if you need further assistance.

Regards,

Dinesh

[v-dineshya]

Hi @Mha35 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

Regards,

Dinesh

[bhanu_gautam]

@Mha35 Use Pivot and Group by in Power Query

Transform the Data: Use Power Query to pivot and aggregate the data as needed.

Transform the Data
In the Power Query Editor, select your table.
Pivot the data to get the desired structure.
Transformations in Power Query Editor:
Pivot the Data:

Select the Date column.
Click on Transform -> Pivot Column.
In the Pivot Column dialog, select Company as the value column and Value as the aggregation.
Group By:

Select the Date column.
Click on Transform -> Group By.
In the Group By dialog, add a new aggregation for each combination of Company, Currency, and Product.
Create Custom Columns:

Create custom columns to calculate the sum for each combination of Company, Currency, and Product.

Create the Matrix Visualization
Go back to the main Power BI interface.
Select the Matrix visualization.
Drag the Date column to the Rows.
Drag the Company column to the Columns.
Drag the Currency column to the Columns.
Drag the Product column to the Columns.
Drag the Value column to the Values.

[Mha35]

Hi!

Many thanks for the step by step guide!

Sorry, im kinda new to powerBI. But when i was trying to follow your instructions:

Select the Date column.
Click on Transform -> Pivot Column.
In the Pivot Column dialog, select Company as the value column and Value as the aggregation.

I couldnt find the "Value" as the aggregation

But even so, the next step consists in selecting the date column again. Should i select all the dates or only one?

[bhanu_gautam]

Select the Date column.
Go to the "Transform" tab and click on "Pivot Column."
In the Pivot Column dialog, select Company as the value column and Value as the aggregation

All dates means basically date column