Forum Discussion
Solved
Quick Measure help
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
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]) ) )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%
7 Replies
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-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601i am not good with power BI dax, can you help on this & share the pbix with the solution?
will apreciate it
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.
