Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to 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.
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThank 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! 😁
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThis 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.
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThank you! Yes a solid model really help things along nicely.
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |