Forum Discussion
Helper ISelf-join to create a measure, columns virtual
Microsoft EmployeeIf you can pivot the data so that you have one row per account with a Actual and Budget column, then the variance is a simple calculated column subtracting Actual from Budget.
I'm not sure I understand where the challenge is here, but obviously I'm missing something. Can you try to clarify?
Helper Idkay84_PowerBI My second paragraph should answer your question if you read it carefully all the way through.
There are two ways I can model the columns in this data, and there are advantages and disadvantages to each. I currently lean toward the "virtual" approach for the reasons I mentioned.
- dkay84_PowerBI
I haven't worked with virtual columns or tables within Power BI, but if you can connect to them through PBI desktop, I don't understand why you can't just work with them the way I described. I am basing my assessment on the example data you gave. If this is the form that your data appears in once loaded into PBI, you should be able to follow the steps I described.
- uBoatCaptain
Thanks for you replies @dkay84_PowerBI I wonder if we are talking past each other? If we were talking pivot tables rather than power BI we can pivot the data by dropping the column attribute on the pivot table columns. From there maybe there is a way to define a calculated difference between the two columns using the capabilities of pivot tables in Excel. But I am not aware of anything other than DAX to do the calculations in Power BI. For example, a Matrix could pivot and display the virtual columns as columns, but a Matrix has no calculation capability on its own, so it cannot calculate the difference between the columns.
I am wondering if you know something I do not that would help me make use of your approach. Perhaps you are thinking of a different approach with DAX than the one v-huizhn-msft suggested?
- dkay84_PowerBICan you use the Query Editor to do the pivot and then load that table to the data model where you can use DAX for the calculations?