Sum values into specific groups and then pivot them into the original values column.

[CallumMurfet]

Hello,

My query is that I want to sum the values into the broader groups under the LEP column. I then want to pivot these values into the original value column, such that they include the values of geographies in the geography column as well as the summed values of the LEP geographies. 

Thank you for your help

[amitchandak]

@CallumMurfet , The information you have provided is not making the problem clear to me. Can you please explain with an example.
Can you share sample data and sample output in table format?

You should be able to do that in matrix visual

https://www.burningsuit.co.uk/blog/2019/04/7-secrets-of-the-matrix-visual/

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

Appreciate your Kudos.

[CallumMurfet]

Hello,

See the model in the attached images.

There are 4 geographies in the geography lookup table ;Country , Region , LEP and Local authority. I have geography filters for each of the 4 geographies, as seen in the 4 boxes filters. 

In the Age profile data table , there is a geography column, which is connected to the geography lookup, but there is only Country, Region and Local authority and not LEP in this column.

I have created a visualisation from Age profile data table that shows population by age group and have used the geography lookup as the legend. Unfortunately, because there are no “LEPs” in the geography column of the Age profile table, when I make a LEP selection on the filters above it does not appear in the visual.

LEP’s are made up of local authorities which are included in the Age profile data. And so I want to use the LEP lookup table to sum the local authorities data to form LEPs. I then want to introduce this data into the chart, so that I can make a selection for LEPs from the geography lookup so that they also appear in the chart. Im happy to share the pbix file but not sure how I share it with you.