Matrix with Multiple Group

[chinma]

Dear Team,

I have a table with values for Date,WarehouseID,TotalInvoice,TotalUnAllocated,UnAllocatedSafetyStock,UnAllocatedRAW, (TotalUnAllocated-UnAllocatedSafetyStock-UnAllocatedRAW) as UnAllocated_Less_RM_SS. I need to create a matrix with Date as Column and (TotalInvoice,TotalUnAllocated,UnAllocatedSafetyStock,UnAllocatedRAW,UnAllocated_Less_RM_SS) as Rows for each WarhouseIDs. Also I need to have GrandTotal for all warehouses for each date at the bottom of matrix.

Here is the sample data:

Expected Result:

Any help would be appreciated greatly.

Thanks in advance.

[chinma]

@PijushRoy  Here is the link for sample data https://drive.google.com/file/d/1wfomYish9U5AVZzcqUxYKVzal-CFAgYO/view?usp=sharing

Thank you.

[PijushRoy]

Hi @chinma 

Please add warehouse in row header before ID 
Please find the pbix link 

If your requirement is solved, Please mark as SOLUTION

Thanks
Pijush

[chinma]

@PijushRoy  Thank you for the quick response. But my request is "On expanding" each warehouse ID, it should show the rows for " Total Invoice,Total Un Allocated, UnAllocated Safety Stock,UnAllocated RAW material, UnAllocated_Less_RM_SS)

same as given below:

Also it should show the Total as "ALL Locations" with details in rows.

eg:

Let me know if you have any solution on this.

Thank you.

[PijushRoy]

Hi @chinma 

https://drive.google.com/file/d/1cX1U_QjidoMZrxsKwJM4UfEEvo0wSztb/view?usp=drive_link

If your requirement is solved, please make sure to MARK AS SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.

Thanks
Pijush

[chinma]

@PijushRoy  Thank you for your solution . Its almost worked except that I need to have expandable section for Total at the bottm of the matrix as " All locations" for sum of each attributes with details as shown below:

Any help would be grateful.

Thank you.

[PijushRoy]

Hi @chinma 

For this requirement, you have to change the row order

https://drive.google.com/file/d/1cX1U_QjidoMZrxsKwJM4UfEEvo0wSztb/view?usp=drive_link

If your requirement is solved, please make sure to MARK AS SOLUTION and help other users find the solution quickly. Please hit the LIKE button if this comment helps you.

Thanks
Pijush

[chinma]

@PijushRoy My requirement was to have detail section for Total as well.  It should be the sum of Total_Un_Allocated, Sum of Total Invoice....etc for all the warehouse IDs for each day.

eg:

Please let me know how to achieve this.

Thank you.

[PijushRoy]

Hi @chinma 

As per my idea, you can combine two tables to achieve this

If your requirement is solved, please make sure to MARK AS SOLUTION and help other users find the solution quickly. Please hit the LIKE button if this comment helps you.

Thanks
Pijush

[PijushRoy]

Hi @chinma 

Sample data link is not working, please check