The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.

**Save €200 with code MSCUST on top of early bird pricing!**

- Power BI forums
- Updates
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

- Power BI forums
- Forums
- Get Help with Power BI
- Desktop
- Re: Difference between 2 sub columns in a matrix

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Anonymous

Not applicable

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Difference between 2 sub columns in a matrix

12-05-2019
10:46 PM

I have a matrix broken shown as such:

I would like to create a difference column that would look like this in excel pivot table:

This is how the raw data looks like:

Let me know if any part is not clear. Happy to explain further.

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-06-2019
12:41 AM

@Anonymous

i got it

duplicate data source Table1 as 'Table 1 (2)'

then create new table

`New Table = union('Table1';summarize('Table1 (2)';'Table1 (2)'[Vessel];'Table1 (2)'[Voyage];"Data Type";"zDifference";"Revenue";sumx('Table1 (2)';(calculate(SUM('Table1 (2)'[Revenue]);'Table1 (2)'[Data Type]="Actual")-calculate(SUM('Table1 (2)'[Revenue]);'Table1 (2)'[Data Type]="Estimate")));"Cost";sumx('Table1 (2)';(calculate(SUM('Table1 (2)'[Cost]);'Table1 (2)'[Data Type]="Actual")-calculate(SUM('Table1 (2)'[Cost]);'Table1 (2)'[Data Type]="Estimate")))))`

aaand

all fields are from New Table, of course

*do not hesitate to give a kudo to useful posts and mark solutions as solution*

10 REPLIES 10

Anonymous

Not applicable

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-05-2019
11:05 PM

@Anonymous please use below measures

```
_cost =
VAR _actual = CALCULATE(SUM('Table'[Cost]),FILTER(ALLEXCEPT('Table','Table'[Vessel],'Table'[Voyage]),'Table'[data Type]="Actual"))
VAR _estimate = CALCULATE(SUM('Table'[Cost]),FILTER(ALLEXCEPT('Table','Table'[Vessel],'Table'[Voyage]),'Table'[data Type]="Estimate"))
RETURN _actual-_estimate
```

```
_Revenue =
VAR _actual = CALCULATE(SUM('Table'[Revenue]),FILTER(ALLEXCEPT('Table','Table'[Vessel],'Table'[Voyage]),'Table'[data Type]="Actual"))
VAR _estimate = CALCULATE(SUM('Table'[Revenue]),FILTER(ALLEXCEPT('Table','Table'[Vessel],'Table'[Voyage]),'Table'[data Type]="Estimate"))
RETURN _actual-_estimate
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-05-2019
11:16 PM

Hi Vimal,

Thanks for your solution. But after inserting, it does not give me the intended result either. Please advise.

I am expecting get a result like this:

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-05-2019
10:57 PM

Hi @Anonymous

try 2 measures

```
RevenueMeasure = calculate(SUM(Table1[Revenue]);Table1[Data Type]="Actual")-calculate(SUM(Table1[Revenue]);Table1[Data Type]="Estimate")
CostMeasure = calculate(SUM(Table1[Cost]);Table1[Data Type]="Actual")-calculate(SUM(Table1[Cost]);Table1[Data Type]="Estimate")
```

*do not hesitate to give a kudo to useful posts and mark solutions as solution*

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-05-2019
11:04 PM

I tried that before, but putting it into the matrix table will result in the difference appearing within each subtype, which is not whtat I want.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-05-2019
11:12 PM

@Anonymous

create 4 more measures

```
CostEstimate = calculate(SUM(Table1[Cost]);Table1[Data Type]="Estimate")
CostActual = calculate(SUM(Table1[Cost]);Table1[Data Type]="Actual")
RevenueActual = calculate(SUM(Table1[Revenue]);Table1[Data Type]="Actual")
RevenueEstimate = calculate(SUM(Table1[Revenue]);Table1[Data Type]="Estimate")
```

then

```
RevenueDifference = [RevenueActual]-[RevenueEstimate]
CostDifference = [CostActual]-[CostEstimate]
```

then visual

*do not hesitate to give a kudo to useful posts and mark solutions as solution*

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-05-2019
11:20 PM

Hi @az38 ,

I understand your solution, so you're suggesting leveling the hierarchy of datatype into the same level as the measures so as to create direct simple subtraction.

That can work but visually will not look as structured as I intended. Is there no other way to obtain the way I like in the excel output?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-06-2019
12:41 AM

@Anonymous

i got it

duplicate data source Table1 as 'Table 1 (2)'

then create new table

`New Table = union('Table1';summarize('Table1 (2)';'Table1 (2)'[Vessel];'Table1 (2)'[Voyage];"Data Type";"zDifference";"Revenue";sumx('Table1 (2)';(calculate(SUM('Table1 (2)'[Revenue]);'Table1 (2)'[Data Type]="Actual")-calculate(SUM('Table1 (2)'[Revenue]);'Table1 (2)'[Data Type]="Estimate")));"Cost";sumx('Table1 (2)';(calculate(SUM('Table1 (2)'[Cost]);'Table1 (2)'[Data Type]="Actual")-calculate(SUM('Table1 (2)'[Cost]);'Table1 (2)'[Data Type]="Estimate")))))`

aaand

all fields are from New Table, of course

*do not hesitate to give a kudo to useful posts and mark solutions as solution*

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

05-22-2020
07:28 AM

Hi @az38 , @Anonymous

This is exactly I want to see in my solution . Can you please explain the 'New Table' concept in easier terms. I am quite new to PowerBI

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-06-2019
12:59 AM

Hi @az38

Yes I think this is what I am looking for and it should work for me!!

Thanks so much for your help!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

12-06-2019
12:18 AM

@Anonymous

i think it is the easiest

but. you can dublicate your data source. make a group by vessel/voyage, calculate rev and cost difference, fill the field Data Type as "Difference" (or "zDifference" for correct cloumn ort), then union tables (initial and grouped), then add Data Type column in the matrix as columns.

wow. maybe you could understand, because im not sure that was good explanation of my idea 🙂

*do not hesitate to give a kudo to useful posts and mark solutions as solution*

Featured Topics

Top Solution Authors

User | Count |
---|---|

110 | |

109 | |

84 | |

47 | |

39 |

Top Kudoed Authors

User | Count |
---|---|

195 | |

82 | |

75 | |

60 | |

57 |