Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
@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.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |