Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
1up
Helper III
Helper III

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

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.

 

RegionSalesYear
Europe1002019
Europe Extra1052019
North America1102019
North America Extra1232019
Europe1102020
Europe Extra942020
North America1252020
North America Extra1302020
   
   
Wanted Result  
 Sales
Region20192020
Europe100110
Europe Extra10594
Europe Total205204
North America110125
North America Extra123130
North America Total233255
1 ACCEPTED SOLUTION
edhans
Super User
Super User

This is a simple measure @1up 

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

It returns this:

edhans_0-1613515867320.png

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.

edhans_1-1613515924017.png

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

edhans_2-1613515970657.png

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.

edhans_3-1613516138994.png

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

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

4 REPLIES 4
1up
Helper III
Helper III

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

Cheers, 1up

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! 😁



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
edhans
Super User
Super User

This is a simple measure @1up 

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

It returns this:

edhans_0-1613515867320.png

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.

edhans_1-1613515924017.png

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

edhans_2-1613515970657.png

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.

edhans_3-1613516138994.png

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

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors