Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I created a measure to estimate the variance between "Actual" and "Budget" expenses. The equation is
The data table example:
Account | Scenario | Values | GDR Type |
1000 | Actual | 100 | COR |
1000 | Budget | 100 | COR |
1000 | Actual | 100 | Revenue |
1000 | Budget | 100 | Revenue |
The measures created:
Account | Actual | Budget | Variance |
1000 | 100 | 100 | 0 |
I have two viz one with Actuals and Budget and the other with variance only. The Actual and Budget have Ebitda.
Thanks
@Anonymous , Did not get it completely. But if you do want variance as roe you have to something like this
Union (
Summarize(filter(Table,Sceniro="Budget"),Table[Account],"Name","Budget","Value",Sum(Table[Value])),
Summarize(filter(Table,Sceniro="Actual"),Table[Account],"Name","Actual","Value",Sum(Table[Value])),
Summarize(Table,Table[Account],"Name","Variance","Value",Sumx(Table,if(Sceniro="Actual",-1*Table[Value],1*Table[Value])))
)
Or better
Union (
Summarize(Table,Table[Account],Table[Scenario],"Value",Sum(Table[Value])),
Summarize(Table,Table[Account],"Scenario","Variance","Value",Sumx(Table,if(Sceniro="Actual",-1*Table[Value],1*Table[Value])))
)
Thank you for the reply and sorry for the confusion. To further clarify: I am trying to get Actual, Budget, and Variance in one table.
To display Actual and Budget, I am using EBITDA = [Gross Profit]-[SGA] measure but for variance, I am using Variance = calculate([EBITDA], filter('values',[EBITDA]<>0),filter('values','values'[Scenario] = "Actual")) - calculate([EBITDA], filter('values',[EBITDA]<>0),filter('values','values'[Scenario] = "Budget")) measure.
Due to two different measures, I end up getting two different Viz. One for Actual and Budget and one for Variance. I want all of these two to be in one Viz.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |