Quick Measure help

[vjnvinod]

Dear Experts,

I have visual matrix like below

Expected outcome should be something like below for each category

the one highlighted in the red below is what is my expected outcome

var_H=Hours Actuals-Hours Planned

VarH%=Hours Actuals/Hours Planned-1

here is my pbix file attached, any help to get that measure will be very helpful

basically variance should come as row

Pbix file 

[amitchandak · Accepted solution]

Access same link

Table 2 = UNION(
	SUMMARIZE(
	data,data[Eng#.1],"Custom","Hour Variance %"
,"1",(divide(sumx(data,if(data[Custom]="Hours Actuals",[1],0)),sumx(data,if(data[Custom]="Hours Planned",[1],0)),0)-1)
,"2",(divide(sumx(data,if(data[Custom]="Hours Actuals",[2],0)),sumx(data,if(data[Custom]="Hours Planned",[2],0)),0)-1)
,"3",(divide(sumx(data,if(data[Custom]="Hours Actuals",[3],0)),sumx(data,if(data[Custom]="Hours Planned",[3],0)),0)-1)
,"4",(divide(sumx(data,if(data[Custom]="Hours Actuals",[4],0)),sumx(data,if(data[Custom]="Hours Planned",[4],0)),0)-1)
,"5",(divide(sumx(data,if(data[Custom]="Hours Actuals",[5],0)),sumx(data,if(data[Custom]="Hours Planned",[5],0)),0)-1)
,"6",(divide(sumx(data,if(data[Custom]="Hours Actuals",[6],0)),sumx(data,if(data[Custom]="Hours Planned",[6],0)),0)-1)
,"7",(divide(sumx(data,if(data[Custom]="Hours Actuals",[7],0)),sumx(data,if(data[Custom]="Hours Planned",[7],0)),0)-1)
,"8",(divide(sumx(data,if(data[Custom]="Hours Actuals",[8],0)),sumx(data,if(data[Custom]="Hours Planned",[8],0)),0)-1)
,"9",(divide(sumx(data,if(data[Custom]="Hours Actuals",[9],0)),sumx(data,if(data[Custom]="Hours Planned",[9],0)),0)-1)
,"10",(divide(sumx(data,if(data[Custom]="Hours Actuals",[10],0)),sumx(data,if(data[Custom]="Hours Planned",[10],0)),0)-1)
,"11",(divide(sumx(data,if(data[Custom]="Hours Actuals",[11],0)),sumx(data,if(data[Custom]="Hours Planned",[11],0)),0)-1)
,"12",(divide(sumx(data,if(data[Custom]="Hours Actuals",[12],0)),sumx(data,if(data[Custom]="Hours Planned",[12],0)),0)-1)

),	SUMMARIZE(
	data,data[Eng#.1],"Custom","Time Variance %"
,"1",(divide(sumx(data,if(data[Custom]="Time Actuals",[1],0)),sumx(data,if(data[Custom]="Time Planned",[1],0)),0)-1)
,"2",(divide(sumx(data,if(data[Custom]="Time Actuals",[2],0)),sumx(data,if(data[Custom]="Time Planned",[2],0)),0)-1)
,"3",(divide(sumx(data,if(data[Custom]="Time Actuals",[3],0)),sumx(data,if(data[Custom]="Time Planned",[3],0)),0)-1)
,"4",(divide(sumx(data,if(data[Custom]="Time Actuals",[4],0)),sumx(data,if(data[Custom]="Time Planned",[4],0)),0)-1)
,"5",(divide(sumx(data,if(data[Custom]="Time Actuals",[5],0)),sumx(data,if(data[Custom]="Time Planned",[5],0)),0)-1)
,"6",(divide(sumx(data,if(data[Custom]="Time Actuals",[6],0)),sumx(data,if(data[Custom]="Time Planned",[6],0)),0)-1)
,"7",(divide(sumx(data,if(data[Custom]="Time Actuals",[7],0)),sumx(data,if(data[Custom]="Time Planned",[7],0)),0)-1)
,"8",(divide(sumx(data,if(data[Custom]="Time Actuals",[8],0)),sumx(data,if(data[Custom]="Time Planned",[8],0)),0)-1)
,"9",(divide(sumx(data,if(data[Custom]="Time Actuals",[9],0)),sumx(data,if(data[Custom]="Time Planned",[9],0)),0)-1)
,"10",(divide(sumx(data,if(data[Custom]="Time Actuals",[10],0)),sumx(data,if(data[Custom]="Time Planned",[10],0)),0)-1)
,"11",(divide(sumx(data,if(data[Custom]="Time Actuals",[11],0)),sumx(data,if(data[Custom]="Time Planned",[11],0)),0)-1)
,"12",(divide(sumx(data,if(data[Custom]="Time Actuals",[12],0)),sumx(data,if(data[Custom]="Time Planned",[12],0)),0)-1)

),SUMMARIZE(
	data,data[Eng#.1],"Custom","Expense Variance %"
,"1",(divide(sumx(data,if(data[Custom]="Expense Actuals",[1],0)),sumx(data,if(data[Custom]="Expense Planned",[1],0)),0)-1)
,"2",(divide(sumx(data,if(data[Custom]="Expense Actuals",[2],0)),sumx(data,if(data[Custom]="Expense Planned",[2],0)),0)-1)
,"3",(divide(sumx(data,if(data[Custom]="Expense Actuals",[3],0)),sumx(data,if(data[Custom]="Expense Planned",[3],0)),0)-1)
,"4",(divide(sumx(data,if(data[Custom]="Expense Actuals",[4],0)),sumx(data,if(data[Custom]="Expense Planned",[4],0)),0)-1)
,"5",(divide(sumx(data,if(data[Custom]="Expense Actuals",[5],0)),sumx(data,if(data[Custom]="Expense Planned",[5],0)),0)-1)
,"6",(divide(sumx(data,if(data[Custom]="Expense Actuals",[6],0)),sumx(data,if(data[Custom]="Expense Planned",[6],0)),0)-1)
,"7",(divide(sumx(data,if(data[Custom]="Expense Actuals",[7],0)),sumx(data,if(data[Custom]="Expense Planned",[7],0)),0)-1)
,"8",(divide(sumx(data,if(data[Custom]="Expense Actuals",[8],0)),sumx(data,if(data[Custom]="Expense Planned",[8],0)),0)-1)
,"9",(divide(sumx(data,if(data[Custom]="Expense Actuals",[9],0)),sumx(data,if(data[Custom]="Expense Planned",[9],0)),0)-1)
,"10",(divide(sumx(data,if(data[Custom]="Expense Actuals",[10],0)),sumx(data,if(data[Custom]="Expense Planned",[10],0)),0)-1)
,"11",(divide(sumx(data,if(data[Custom]="Expense Actuals",[11],0)),sumx(data,if(data[Custom]="Expense Planned",[11],0)),0)-1)
,"12",(divide(sumx(data,if(data[Custom]="Expense Actuals",[12],0)),sumx(data,if(data[Custom]="Expense Planned",[12],0)),0)-1)

),
SUMMARIZE(
	data,data[Eng#.1],"Custom","Hour Variance"
,"1",(sumx(data,if(data[Custom]="Hours Actuals",[1],0))- sumx(data,if(data[Custom]="Hours Planned",[1],0)))
,"2",(sumx(data,if(data[Custom]="Hours Actuals",[2],0))- sumx(data,if(data[Custom]="Hours Planned",[2],0)))
,"3",(sumx(data,if(data[Custom]="Hours Actuals",[3],0))- sumx(data,if(data[Custom]="Hours Planned",[3],0)))
,"4",(sumx(data,if(data[Custom]="Hours Actuals",[4],0))- sumx(data,if(data[Custom]="Hours Planned",[4],0)))
,"5",(sumx(data,if(data[Custom]="Hours Actuals",[5],0))- sumx(data,if(data[Custom]="Hours Planned",[5],0)))
,"6",(sumx(data,if(data[Custom]="Hours Actuals",[6],0))- sumx(data,if(data[Custom]="Hours Planned",[6],0)))
,"7",(sumx(data,if(data[Custom]="Hours Actuals",[7],0))- sumx(data,if(data[Custom]="Hours Planned",[7],0)))
,"8",(sumx(data,if(data[Custom]="Hours Actuals",[8],0))- sumx(data,if(data[Custom]="Hours Planned",[8],0)))
,"9",(sumx(data,if(data[Custom]="Hours Actuals",[9],0))- sumx(data,if(data[Custom]="Hours Planned",[9],0)))
,"10",(sumx(data,if(data[Custom]="Hours Actuals",[10],0))- sumx(data,if(data[Custom]="Hours Planned",[10],0)))
,"11",(sumx(data,if(data[Custom]="Hours Actuals",[11],0))- sumx(data,if(data[Custom]="Hours Planned",[11],0)))
,"12",(sumx(data,if(data[Custom]="Hours Actuals",[12],0))- sumx(data,if(data[Custom]="Hours Planned",[12],0)))

),	SUMMARIZE(
	data,data[Eng#.1],"Custom","Time Variance"
,"1",(sumx(data,if(data[Custom]="Time Actuals",[1],0))- sumx(data,if(data[Custom]="Time Planned",[1],0)))
,"2",(sumx(data,if(data[Custom]="Time Actuals",[2],0))- sumx(data,if(data[Custom]="Time Planned",[2],0)))
,"3",(sumx(data,if(data[Custom]="Time Actuals",[3],0))- sumx(data,if(data[Custom]="Time Planned",[3],0)))
,"4",(sumx(data,if(data[Custom]="Time Actuals",[4],0))- sumx(data,if(data[Custom]="Time Planned",[4],0)))
,"5",(sumx(data,if(data[Custom]="Time Actuals",[5],0))- sumx(data,if(data[Custom]="Time Planned",[5],0)))
,"6",(sumx(data,if(data[Custom]="Time Actuals",[6],0))- sumx(data,if(data[Custom]="Time Planned",[6],0)))
,"7",(sumx(data,if(data[Custom]="Time Actuals",[7],0))- sumx(data,if(data[Custom]="Time Planned",[7],0)))
,"8",(sumx(data,if(data[Custom]="Time Actuals",[8],0))- sumx(data,if(data[Custom]="Time Planned",[8],0)))
,"9",(sumx(data,if(data[Custom]="Time Actuals",[9],0))- sumx(data,if(data[Custom]="Time Planned",[9],0)))
,"10",(sumx(data,if(data[Custom]="Time Actuals",[10],0))- sumx(data,if(data[Custom]="Time Planned",[10],0)))
,"11",(sumx(data,if(data[Custom]="Time Actuals",[11],0))- sumx(data,if(data[Custom]="Time Planned",[11],0)))
,"12",(sumx(data,if(data[Custom]="Time Actuals",[12],0))- sumx(data,if(data[Custom]="Time Planned",[12],0)))

),SUMMARIZE(
	data,data[Eng#.1],"Custom","Expense Variance"
,"1",(sumx(data,if(data[Custom]="Expense Actuals",[1],0))- sumx(data,if(data[Custom]="Expense Planned",[1],0)))
,"2",(sumx(data,if(data[Custom]="Expense Actuals",[2],0))- sumx(data,if(data[Custom]="Expense Planned",[2],0)))
,"3",(sumx(data,if(data[Custom]="Expense Actuals",[3],0))- sumx(data,if(data[Custom]="Expense Planned",[3],0)))
,"4",(sumx(data,if(data[Custom]="Expense Actuals",[4],0))- sumx(data,if(data[Custom]="Expense Planned",[4],0)))
,"5",(sumx(data,if(data[Custom]="Expense Actuals",[5],0))- sumx(data,if(data[Custom]="Expense Planned",[5],0)))
,"6",(sumx(data,if(data[Custom]="Expense Actuals",[6],0))- sumx(data,if(data[Custom]="Expense Planned",[6],0)))
,"7",(sumx(data,if(data[Custom]="Expense Actuals",[7],0))- sumx(data,if(data[Custom]="Expense Planned",[7],0)))
,"8",(sumx(data,if(data[Custom]="Expense Actuals",[8],0))- sumx(data,if(data[Custom]="Expense Planned",[8],0)))
,"9",(sumx(data,if(data[Custom]="Expense Actuals",[9],0))- sumx(data,if(data[Custom]="Expense Planned",[9],0)))
,"10",(sumx(data,if(data[Custom]="Expense Actuals",[10],0))- sumx(data,if(data[Custom]="Expense Planned",[10],0)))
,"11",(sumx(data,if(data[Custom]="Expense Actuals",[11],0))- sumx(data,if(data[Custom]="Expense Planned",[11],0)))
,"12",(sumx(data,if(data[Custom]="Expense Actuals",[12],0))- sumx(data,if(data[Custom]="Expense Planned",[12],0)))

),
	SUMMARIZE(
	data,data[Eng#.1],data[Custom]
	,"1",sumx(data,[1])
	,"2",sumx(data,[2])
	,"3",sumx(data,[3])
	,"4",sumx(data,[4])
	,"5",sumx(data,[5])
	,"6",sumx(data,[6])
	,"7",sumx(data,[7])
	,"8",sumx(data,[8])
	,"9",sumx(data,[9])
	,"10",sumx(data,[10])
	,"11",sumx(data,[11])
	,"12",sumx(data,[12])
	)
)

[amitchandak]

You can create a new table

Table = UNION(SUMMARIZE(data,data[Eng#.1],"Custom","Hour Variance","1",sumx(data,if(data[Custom]="Hour Planned",[1],if(data[Custom]="Hour Actuals",-1*[1],0)))),SUMMARIZE(data,data[Eng#.1],data[Custom],"1",sumx(data,[1])))

You need to add more summarize table for other variance or can develop some if logic.

You can avoid summarizing of exiting data using addcolumns

https://docs.microsoft.com/en-us/dax/addcolumns-function-dax

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[vjnvinod]

@amitchandak 

i am not good with power BI dax, can you help on this & share the pbix with the solution?

will apreciate it

[amitchandak]

Please check

https://www.dropbox.com/s/cezh9xvdvdc4tvu/my%20test.pbix?dl=0

[vjnvinod]

@amitchandak 

I noticed your measure is very close.

But i could only see the details for "1"  and missing for "2", "3","4",....... "12"

and also missing  some of the variance i needed

VarH%=Hours Actuals/Hours Planned-1

VarP=Expense Actuals-Expense Planned

VarP%=Expense Actuals/Expense Planned-1

VarT=Time actuals-Time planned

VarT%=Time actuals/Time planned-1

Can you see if this is feasible to include & if you could share the pbix, it will be great help

[amitchandak · Accepted solution]

Access same link

Table 2 = UNION(
	SUMMARIZE(
	data,data[Eng#.1],"Custom","Hour Variance %"
,"1",(divide(sumx(data,if(data[Custom]="Hours Actuals",[1],0)),sumx(data,if(data[Custom]="Hours Planned",[1],0)),0)-1)
,"2",(divide(sumx(data,if(data[Custom]="Hours Actuals",[2],0)),sumx(data,if(data[Custom]="Hours Planned",[2],0)),0)-1)
,"3",(divide(sumx(data,if(data[Custom]="Hours Actuals",[3],0)),sumx(data,if(data[Custom]="Hours Planned",[3],0)),0)-1)
,"4",(divide(sumx(data,if(data[Custom]="Hours Actuals",[4],0)),sumx(data,if(data[Custom]="Hours Planned",[4],0)),0)-1)
,"5",(divide(sumx(data,if(data[Custom]="Hours Actuals",[5],0)),sumx(data,if(data[Custom]="Hours Planned",[5],0)),0)-1)
,"6",(divide(sumx(data,if(data[Custom]="Hours Actuals",[6],0)),sumx(data,if(data[Custom]="Hours Planned",[6],0)),0)-1)
,"7",(divide(sumx(data,if(data[Custom]="Hours Actuals",[7],0)),sumx(data,if(data[Custom]="Hours Planned",[7],0)),0)-1)
,"8",(divide(sumx(data,if(data[Custom]="Hours Actuals",[8],0)),sumx(data,if(data[Custom]="Hours Planned",[8],0)),0)-1)
,"9",(divide(sumx(data,if(data[Custom]="Hours Actuals",[9],0)),sumx(data,if(data[Custom]="Hours Planned",[9],0)),0)-1)
,"10",(divide(sumx(data,if(data[Custom]="Hours Actuals",[10],0)),sumx(data,if(data[Custom]="Hours Planned",[10],0)),0)-1)
,"11",(divide(sumx(data,if(data[Custom]="Hours Actuals",[11],0)),sumx(data,if(data[Custom]="Hours Planned",[11],0)),0)-1)
,"12",(divide(sumx(data,if(data[Custom]="Hours Actuals",[12],0)),sumx(data,if(data[Custom]="Hours Planned",[12],0)),0)-1)

),	SUMMARIZE(
	data,data[Eng#.1],"Custom","Time Variance %"
,"1",(divide(sumx(data,if(data[Custom]="Time Actuals",[1],0)),sumx(data,if(data[Custom]="Time Planned",[1],0)),0)-1)
,"2",(divide(sumx(data,if(data[Custom]="Time Actuals",[2],0)),sumx(data,if(data[Custom]="Time Planned",[2],0)),0)-1)
,"3",(divide(sumx(data,if(data[Custom]="Time Actuals",[3],0)),sumx(data,if(data[Custom]="Time Planned",[3],0)),0)-1)
,"4",(divide(sumx(data,if(data[Custom]="Time Actuals",[4],0)),sumx(data,if(data[Custom]="Time Planned",[4],0)),0)-1)
,"5",(divide(sumx(data,if(data[Custom]="Time Actuals",[5],0)),sumx(data,if(data[Custom]="Time Planned",[5],0)),0)-1)
,"6",(divide(sumx(data,if(data[Custom]="Time Actuals",[6],0)),sumx(data,if(data[Custom]="Time Planned",[6],0)),0)-1)
,"7",(divide(sumx(data,if(data[Custom]="Time Actuals",[7],0)),sumx(data,if(data[Custom]="Time Planned",[7],0)),0)-1)
,"8",(divide(sumx(data,if(data[Custom]="Time Actuals",[8],0)),sumx(data,if(data[Custom]="Time Planned",[8],0)),0)-1)
,"9",(divide(sumx(data,if(data[Custom]="Time Actuals",[9],0)),sumx(data,if(data[Custom]="Time Planned",[9],0)),0)-1)
,"10",(divide(sumx(data,if(data[Custom]="Time Actuals",[10],0)),sumx(data,if(data[Custom]="Time Planned",[10],0)),0)-1)
,"11",(divide(sumx(data,if(data[Custom]="Time Actuals",[11],0)),sumx(data,if(data[Custom]="Time Planned",[11],0)),0)-1)
,"12",(divide(sumx(data,if(data[Custom]="Time Actuals",[12],0)),sumx(data,if(data[Custom]="Time Planned",[12],0)),0)-1)

),SUMMARIZE(
	data,data[Eng#.1],"Custom","Expense Variance %"
,"1",(divide(sumx(data,if(data[Custom]="Expense Actuals",[1],0)),sumx(data,if(data[Custom]="Expense Planned",[1],0)),0)-1)
,"2",(divide(sumx(data,if(data[Custom]="Expense Actuals",[2],0)),sumx(data,if(data[Custom]="Expense Planned",[2],0)),0)-1)
,"3",(divide(sumx(data,if(data[Custom]="Expense Actuals",[3],0)),sumx(data,if(data[Custom]="Expense Planned",[3],0)),0)-1)
,"4",(divide(sumx(data,if(data[Custom]="Expense Actuals",[4],0)),sumx(data,if(data[Custom]="Expense Planned",[4],0)),0)-1)
,"5",(divide(sumx(data,if(data[Custom]="Expense Actuals",[5],0)),sumx(data,if(data[Custom]="Expense Planned",[5],0)),0)-1)
,"6",(divide(sumx(data,if(data[Custom]="Expense Actuals",[6],0)),sumx(data,if(data[Custom]="Expense Planned",[6],0)),0)-1)
,"7",(divide(sumx(data,if(data[Custom]="Expense Actuals",[7],0)),sumx(data,if(data[Custom]="Expense Planned",[7],0)),0)-1)
,"8",(divide(sumx(data,if(data[Custom]="Expense Actuals",[8],0)),sumx(data,if(data[Custom]="Expense Planned",[8],0)),0)-1)
,"9",(divide(sumx(data,if(data[Custom]="Expense Actuals",[9],0)),sumx(data,if(data[Custom]="Expense Planned",[9],0)),0)-1)
,"10",(divide(sumx(data,if(data[Custom]="Expense Actuals",[10],0)),sumx(data,if(data[Custom]="Expense Planned",[10],0)),0)-1)
,"11",(divide(sumx(data,if(data[Custom]="Expense Actuals",[11],0)),sumx(data,if(data[Custom]="Expense Planned",[11],0)),0)-1)
,"12",(divide(sumx(data,if(data[Custom]="Expense Actuals",[12],0)),sumx(data,if(data[Custom]="Expense Planned",[12],0)),0)-1)

),
SUMMARIZE(
	data,data[Eng#.1],"Custom","Hour Variance"
,"1",(sumx(data,if(data[Custom]="Hours Actuals",[1],0))- sumx(data,if(data[Custom]="Hours Planned",[1],0)))
,"2",(sumx(data,if(data[Custom]="Hours Actuals",[2],0))- sumx(data,if(data[Custom]="Hours Planned",[2],0)))
,"3",(sumx(data,if(data[Custom]="Hours Actuals",[3],0))- sumx(data,if(data[Custom]="Hours Planned",[3],0)))
,"4",(sumx(data,if(data[Custom]="Hours Actuals",[4],0))- sumx(data,if(data[Custom]="Hours Planned",[4],0)))
,"5",(sumx(data,if(data[Custom]="Hours Actuals",[5],0))- sumx(data,if(data[Custom]="Hours Planned",[5],0)))
,"6",(sumx(data,if(data[Custom]="Hours Actuals",[6],0))- sumx(data,if(data[Custom]="Hours Planned",[6],0)))
,"7",(sumx(data,if(data[Custom]="Hours Actuals",[7],0))- sumx(data,if(data[Custom]="Hours Planned",[7],0)))
,"8",(sumx(data,if(data[Custom]="Hours Actuals",[8],0))- sumx(data,if(data[Custom]="Hours Planned",[8],0)))
,"9",(sumx(data,if(data[Custom]="Hours Actuals",[9],0))- sumx(data,if(data[Custom]="Hours Planned",[9],0)))
,"10",(sumx(data,if(data[Custom]="Hours Actuals",[10],0))- sumx(data,if(data[Custom]="Hours Planned",[10],0)))
,"11",(sumx(data,if(data[Custom]="Hours Actuals",[11],0))- sumx(data,if(data[Custom]="Hours Planned",[11],0)))
,"12",(sumx(data,if(data[Custom]="Hours Actuals",[12],0))- sumx(data,if(data[Custom]="Hours Planned",[12],0)))

),	SUMMARIZE(
	data,data[Eng#.1],"Custom","Time Variance"
,"1",(sumx(data,if(data[Custom]="Time Actuals",[1],0))- sumx(data,if(data[Custom]="Time Planned",[1],0)))
,"2",(sumx(data,if(data[Custom]="Time Actuals",[2],0))- sumx(data,if(data[Custom]="Time Planned",[2],0)))
,"3",(sumx(data,if(data[Custom]="Time Actuals",[3],0))- sumx(data,if(data[Custom]="Time Planned",[3],0)))
,"4",(sumx(data,if(data[Custom]="Time Actuals",[4],0))- sumx(data,if(data[Custom]="Time Planned",[4],0)))
,"5",(sumx(data,if(data[Custom]="Time Actuals",[5],0))- sumx(data,if(data[Custom]="Time Planned",[5],0)))
,"6",(sumx(data,if(data[Custom]="Time Actuals",[6],0))- sumx(data,if(data[Custom]="Time Planned",[6],0)))
,"7",(sumx(data,if(data[Custom]="Time Actuals",[7],0))- sumx(data,if(data[Custom]="Time Planned",[7],0)))
,"8",(sumx(data,if(data[Custom]="Time Actuals",[8],0))- sumx(data,if(data[Custom]="Time Planned",[8],0)))
,"9",(sumx(data,if(data[Custom]="Time Actuals",[9],0))- sumx(data,if(data[Custom]="Time Planned",[9],0)))
,"10",(sumx(data,if(data[Custom]="Time Actuals",[10],0))- sumx(data,if(data[Custom]="Time Planned",[10],0)))
,"11",(sumx(data,if(data[Custom]="Time Actuals",[11],0))- sumx(data,if(data[Custom]="Time Planned",[11],0)))
,"12",(sumx(data,if(data[Custom]="Time Actuals",[12],0))- sumx(data,if(data[Custom]="Time Planned",[12],0)))

),SUMMARIZE(
	data,data[Eng#.1],"Custom","Expense Variance"
,"1",(sumx(data,if(data[Custom]="Expense Actuals",[1],0))- sumx(data,if(data[Custom]="Expense Planned",[1],0)))
,"2",(sumx(data,if(data[Custom]="Expense Actuals",[2],0))- sumx(data,if(data[Custom]="Expense Planned",[2],0)))
,"3",(sumx(data,if(data[Custom]="Expense Actuals",[3],0))- sumx(data,if(data[Custom]="Expense Planned",[3],0)))
,"4",(sumx(data,if(data[Custom]="Expense Actuals",[4],0))- sumx(data,if(data[Custom]="Expense Planned",[4],0)))
,"5",(sumx(data,if(data[Custom]="Expense Actuals",[5],0))- sumx(data,if(data[Custom]="Expense Planned",[5],0)))
,"6",(sumx(data,if(data[Custom]="Expense Actuals",[6],0))- sumx(data,if(data[Custom]="Expense Planned",[6],0)))
,"7",(sumx(data,if(data[Custom]="Expense Actuals",[7],0))- sumx(data,if(data[Custom]="Expense Planned",[7],0)))
,"8",(sumx(data,if(data[Custom]="Expense Actuals",[8],0))- sumx(data,if(data[Custom]="Expense Planned",[8],0)))
,"9",(sumx(data,if(data[Custom]="Expense Actuals",[9],0))- sumx(data,if(data[Custom]="Expense Planned",[9],0)))
,"10",(sumx(data,if(data[Custom]="Expense Actuals",[10],0))- sumx(data,if(data[Custom]="Expense Planned",[10],0)))
,"11",(sumx(data,if(data[Custom]="Expense Actuals",[11],0))- sumx(data,if(data[Custom]="Expense Planned",[11],0)))
,"12",(sumx(data,if(data[Custom]="Expense Actuals",[12],0))- sumx(data,if(data[Custom]="Expense Planned",[12],0)))

),
	SUMMARIZE(
	data,data[Eng#.1],data[Custom]
	,"1",sumx(data,[1])
	,"2",sumx(data,[2])
	,"3",sumx(data,[3])
	,"4",sumx(data,[4])
	,"5",sumx(data,[5])
	,"6",sumx(data,[6])
	,"7",sumx(data,[7])
	,"8",sumx(data,[8])
	,"9",sumx(data,[9])
	,"10",sumx(data,[10])
	,"11",sumx(data,[11])
	,"12",sumx(data,[12])
	)
)

[vjnvinod · Accepted solution]

@amitchandak 

this is amazing, appears to be quite complex measure you have created

is it possible to show all of those variance%   numbers as % format, for instance if its 20, its 20%

[Anonymous]

@vjnvinod 

I don't think there is a quick fix for your requirement. If you need VARH and VARH% to show up as lines in you matrix, they need to exists as values in the Custom column.

So I guess is that you need to calculate the values during the data load.