cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## How to summarize / group more than 4 columns with a sum of a column

Hi Team,

I have a scenario to identify the Gap between a sum of ordered items vs storage capacity of a table in the below mentioned format.
I must show the sum of Ordered items for each Quarter split by Product and Region
I have used the Measure to calculate and summarize the data and displayed using the matrix Formula below, however I am unable to get the required overview Column of Capacity / Ordered and Gap (3rd Column in the screenshot highlighted in Yellow).

Any help is greatly appreciated. Thanks in Advance. Please let me know in case you need any other information.

Source Input table format:

 Region Product Period Capacity Ordered India Apple Q1 45 20 India Apple Q1 45 20 India Apple Q2 45 35 India Banana Q1 60 70 India Banana Q2 60 50 India Banana Q2 60 25 USA Apple Q1 45 40 USA Apple Q1 45 55 USA Apple Q2 45 45 USA Banana Q1 60 40 USA Banana Q2 60 50 USA Banana Q2 60 75

Measure Query that I have used:

Msr_TotalOrdered = CALCULATE(sum(InputTable[Ordered]),GROUPBY(InputTable,InputTable[Product],InputTable[Region],InputTable[Period]))

By using the above meausre and using a Matrix table I am getting the output as follows:

1 ACCEPTED SOLUTION
Super User

output

create a caluclated column  in your table :

then use a matrix visual :

finally :

go to visualztion pane , and switch :  switch values to rows to ON .

let me know if this helps .

NB: you can remove totals, subtotals if you donyt need them . just disable these 2  ,.

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

3 REPLIES 3
New Member

Thank you so much @Daniel29195 , your really a champion !! It resolved my question.

Super User

Super User

output

create a caluclated column  in your table :

then use a matrix visual :

finally :

go to visualztion pane , and switch :  switch values to rows to ON .

let me know if this helps .

NB: you can remove totals, subtotals if you donyt need them . just disable these 2  ,.

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.