Grouping data with separate headings & totals

[PowerAppsUser]

Hi,

I have grouped my data based on a column called 'Sector' which comprises of Electricity, Water, Roads, Sewarage, etc. But this is how the grouping is being shown in my table :

I want to show each sector as a separate heading, with all the projects that come under each sector as rows - Like a separate heading for 'Electricity', 'Water', etc. So all other columns will remain the same, and will be shown under each sector. At the end of each sector, I would also like to show the total investment of each sector as a Sum. 

Thanks!

[PowerAppsUser]

If anyone knows what the issue might be, please help.

[amitchandak]

@PowerAppsUser , I a Matrix Visual you can use Sector in Column (and it will Show as header )

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-matrix-visual

Or

You can pivot the sector

https://radacad.com/pivot-and-unpivot-with-power-bi

[PowerAppsUser]

I tried the Matrix table, but Sector as column dint work as I want to show each project and its related details in 1 row, and Sector would be a separate heading under which each project related details would be shown as a row. So I tried Sector as a row, so it worked partially but again there are some issues. Please have a look at my table...

So as you can see above, all sectors are separate headings like Airport, Collection and Transport, Electricity, etc. and they have a row of project details under them, but there are 2 issues here :

1) Such grouping is forcing me to give some mathematical function like SUM, AVG, MIN, MAX, etc for numeric columns. If I choose dont Summarize, it is automatically taking count. So against the Total, the Investment Year is showing Min year 1990, similarly Project Id is showing Count 3, etc. So I want to remove these functions and I want a total for only Investment column.

2) The row showing the Sector Headings (like Airport, Electricity, etc) is showing some value under each column, when that row should be empty as they just consist of headings, i.e., Airport, Electricity, etc. So from where is this data being picked up for headings? (Example, Country Albania for 'Airport' heading, etc)

Thanks!

@amitchandak