Sum column field pairs by year and add the Total as new column field names

[1up]

Hi,

I'd like to use a measure and Sum two entity pairs existing in the same column. Each Sum I'd like to present with a new name, please find example below.

All my fields exist in the same table. I get a correct result using Calculate and Sum and Containstring, however it

1) Shows the same result for all entities (there are like 25 in total in the full list), and

2) I would like the measure to add new Row fields for the totals so it is easy to see the breakdown and total for each region. If anyone suggest to present this in another way it is welcomed as well of course.

Region	Sales	Year
Europe	100	2019
Europe Extra	105	2019
North America	110	2019
North America Extra	123	2019
Europe	110	2020
Europe Extra	94	2020
North America	125	2020
North America Extra	130	2020
Wanted Result
	Sales
Region	2019	2020
Europe	100	110
Europe Extra	105	94
Europe Total	205	204
North America	110	125
North America Extra	123	130
North America Total	233	255

[edhans · Accepted solution]

This is a simple measure @1up 

Total Sales = SUM('Table'[Sales])

It returns this:

The key is to set your model up properly. I added a date to your sales table. I just made the year be Dec 31 of that year.

The location table is a DIM table that explains how to summarize the data:

The Year field in the visual comes from the Date table, not your sales table. That column doesn't even exist anymore - you can see the Power Query code behind me converting that ot a date and getting rid of the Year itself.

Here is my PBIX file.

This is the date table I used - http://bit.ly/DateTableByEd

The key is the DAX is as simple as it could be with a good model. This is a Star Schema, and what Power BI expects. It is designed for it. Microsoft Guidance on Importance of Star Schema

[1up]

Thank you! Yes a solid model really help things along nicely.

[1up]

Thank you for edhans-ing my initial model and for the pedagogic explanation. Good learnings.

Cheers, 1up

[edhans]

Glad to help @1up - Power BI is super easy to use when getting started, but sometimes the best solution is a good model. Then the DAX becomes much easier. Have fun with Power BI! 😁