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

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

Reply
Anonymous
Not applicable

Difference between 2 sub columns in a matrix

I have a matrix broken shown as such:

PowerBI Capture.PNG

 

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

Excel Capture.PNG

This is how the raw data looks like:

 

Excel Data Capture.PNG

 

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

1 ACCEPTED SOLUTION
az38
Community Champion
Community Champion

@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

416018.PNG

all fields are from New Table, of course

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


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

View solution in original post

10 REPLIES 10
Anonymous
Not applicable

@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
Anonymous
Not applicable

Hi Vimal,

 

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

Power Bi Capture (3).PNG

 

I am expecting get a result like this:

 

Excel Capture.PNG

az38
Community Champion
Community Champion

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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Anonymous
Not applicable

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.

 

Power BI Capture (2).PNG

az38
Community Champion
Community Champion

@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

416007_.PNG

 

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

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
Anonymous
Not applicable

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?

az38
Community Champion
Community Champion

@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

416018.PNG

all fields are from New Table, of course

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


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

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

Anonymous
Not applicable

Hi @az38 

 

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

 

Thanks so much for your help!

az38
Community Champion
Community Champion

@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


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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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