Forum Discussion
How do I create calculated column from Dimension fields?
tbucki1 You need Variance in the raw data for your Version. One approach is to create another table. Two columns: Version, Sort
| Version | Sort |
| Actual |
1 |
| Budget | 2 |
| Variance | 3 |
You actually don't need the sort since they're alphabetical, but it's habit.
Relate the Version column to Version column in your original table using the Power Pivot Data Model.
Then use a SWITCH Function to change the Revenue based on the version, and when Version ="Variance", [Variance] measure.
Might need a little tweaking of some of your measure structure and watch out for circular references but it should work.
Not sure I follow. Can you elaborate more on how I would implement SWITCH in this dataset below as an example? Equation example?
Also what if I wanted to put DAX measures for ACTUAL, BUDGET, and Variance on the rows and have Accounts across the columns how would I be able to build columns calcs against Rev and Exp like the ones I show in DAX.
ALSO back to the previous view, what if I wanted to include an additional caculation next to Variance in the example below... Say "Actual/Budget Ratio" how would adding a second calculated measure to the column work. Sorry I am new to switch and am having trouble conceptializing why the need for a second table to join onto my existing fact table...
Thanks!