Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
@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
@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
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:
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
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.
@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
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?
@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
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
Hi @az38
Yes I think this is what I am looking for and it should work for me!!
Thanks so much for your help!
@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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
86 | |
82 | |
70 | |
49 |
User | Count |
---|---|
143 | |
125 | |
108 | |
60 | |
55 |