MS Excel Pivot to Power BI

[muthusaran]

I'm new to Power BI and while learning it, I tried to replacate few existing Microsoft Excel Pivot in PowerBI and succeeded. One of the attached Pivot couldn't be replicated in PowerBI. Please help me with it .

You may give suggestions like after importing the data (this data exists in SQL server), Create Matrix --> Add Rows (fields names) --> Add columns (field names) --> Add Values (field names) for me to try it out. Sample data that I used and corrsponding Pivot is attached here

Data : (Can be in Excel or SQL table) Imported into PowerBI

YearMonth	Product	Batch	CGUnit	Month	Prod Cost	Packing Cost	Cover Cost	MRP	Profit	Expense
202305	Britannia	P01	Chennai01	202301	10	1	1	15	3	0
202305	Britannia	P01	Chennai01	202302	9	1	0.5	10	0	0.5
202305	Britannia	P01	Chennai02	202302	10	0.5	1.5	16	4	0.5
202305	Britannia	P01	Chennai03	202302	11	0.5	1.5	16	5	1
202305	Britannia	P01	Chennai04	202302	9	0.5	1.5	13	2	1
202305	Britannia	P01	Chennai01	202303	9	1	0.5	10	0	0.5
202305	Britannia	P01	Chennai02	202303	10	0.5	1.5	16	4	0.5
202305	Britannia	P01	Chennai03	202303	11	0.5	1.5	16	5	1
202305	Britannia	P01	Chennai05	202303	9	0.5	1.5	13	2	1
202305	Britannia	P01	Chennai01	202304	9	1	0.5	10	0	0.5
202305	Britannia	P01	Chennai05	202304	10	0.5	1.5	16	4	0.5
202305	Britannia	P01	Chennai01	202306	9	1	0.5	10	0	0.5
202305	Britannia	P01	Chennai05	202306	10	0.5	1.5	16	4	0.5
202305	Britannia	P02	Chennai01	202301	10	1	1	15	3	0
202305	Britannia	P02	Chennai01	202302	9	1	0.5	10	0	0.5
202305	Britannia	P02	Chennai02	202302	10	0.5	1.5	16	4	0.5
202305	Britannia	P02	Chennai03	202302	11	0.5	1.5	16	5	1
202305	Britannia	P03	Chennai04	202302	9	0.5	1.5	13	2	1
202305	Britannia	P03	Chennai01	202303	9	1	0.5	10	0	0.5
202305	Britannia	P03	Chennai02	202303	10	0.5	1.5	16	4	0.5
202305	Britannia	P04	Chennai03	202303	11	0.5	1.5	16	5	1
202305	Britannia	P04	Chennai05	202303	9	0.5	1.5	13	2	1
202305	Britannia	P04	Chennai01	202304	9	1	0.5	10	0	0.5
202305	Britannia	P04	Chennai05	202304	10	0.5	1.5	16	4	0.5
202305	Britannia	P04	Chennai01	202306	9	1	0.5	10	0	0.5
202305	Britannia	P04	Chennai05	202306	10	0.5	1.5	16	4	0.5

MS Excel Pivot settings are:

Excel Pivot coming out as

MS Excel Pivot looks like below. For each type of cost, it shows different cost involved per month.

However, If I replicate in PowerBI, i'm seeing like below. Under each Unit, types of amt is listed instead of Types of Amount as above (getting in excel). I need to get same as Excel pivot in Power BI. Please help me the options/suggestions to achieve.

[Anonymous]

Hi @muthusaran ,

Please refer to my pbix file to see if it helps you.

Please learn more about matrix in power bi: Create a matrix visual in Power BI - Power BI | Microsoft Learn

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[muthusaran]

Thanks for the effort.

I need this similar to Excel pivot.

If we have future period, it will keep on appending and we need to drag to right side which is not expected.

Expected Matrix/Table in Power BI: (Same as Excel Pivot below)

  

Below is the Pivot in Excel looks.

PBIX File: I don't see an option to upload. But, my PBIX looks like below

But, My visualization in my pbix is like below