Forum Discussion

tbucki1's avatar
tbucki1
Advocate I
5 years ago

How do I create calculated column from Dimension fields?

I have a data table that has 5 columns: Account, Data, Office, Version, and Value. I have a numer of DAX measures built to show different views of data from thhose columns. Below is a screenshot of my data table on the left and on the right are some simple pivots...

 

What I want to know is how to I create custom columns off to the side of a dimension view. ??

I.e. In view below how would I create Column K "Variance"??

 

If I drag the version field into columns I want to be able to calculate variance to budget across the DAX measures for Rev, Exp, Profit, Margin, etc....

 

Here are the current DAX measures:

Revenue:=CALCULATE(SUM([Value]),'Table2'[Account]="Revenue")

Expense:=CALCULATE(SUM([Value]),'Table2'[Account]="Expense")

Profit:=[Revenue]-[Expense]

Margin:=DIVIDE([Profit],[Revenue],BLANK())

Actual:=CALCULATE(SUM([Value]),'Table2'[Version]="Actual")

Budget:=CALCULATE(SUM([Value]),'Table2'[Version]="Budget")

Variance:=[Actual]-[Budget]

Actual/Budget Ratio:=DIVIDE([Actual],[Budget],BLANK())

 

 

 

 

How would I accomplish this?

 

Need Help

2 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    5 years ago

    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.

    • tbucki1's avatar
      tbucki1
      Advocate I
      5 years ago

      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!