Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
User | Count |
---|---|
70 | |
70 | |
34 | |
23 | |
22 |
User | Count |
---|---|
96 | |
94 | |
50 | |
42 | |
40 |