Forum Discussion
Helper ISelf-join to create a measure, columns virtual
Hi all. I have data from a budget prep system where the columns are virtual in the main fact table. Below is a very much simplified two-row example. In the application this comes from, it takes care of the pivot needed to present each column as a column. A pivot table or a Power BI matrix can do this as well, and I have reports that do this.
The problem occurs when I want to do a simple variance between Last Year Budget and Last Year Actual. That would seem to require a self-join between the two example rows based on account. I could write the SQL to pivot the columns into actual columns when loaded into Power BI, but that has two disadvantages, one being that a lot of zero values get stored, and the other being that there are measures that are identical except for the column on which they are based. If I do the pivot using SQL those measures need to be written redundantly for each column whereas with measures based on the single Data column a measure only needs to be written once. Is there a way to write a measure to essentially do a lookup to a related row within the main fact table?
Account | Column | Data |
123456 | Last Year Budget | 10 |
123456 | Last Year Actual | 9 |
9 Replies
- dkay84_PowerBI
Microsoft Employee
If 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?
uBoatCaptaindkay84_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.
v-huizhn-msftHi uBoatCaptain,
Please try to create a measure using the formula.variance=CALCULATE(Table[value],FILTER(Table,Table[Column]="Actual"))-CALCULATE(Table[value],FILTER(Table,Table[Column]="Budget"))
If it does not work, please share more details for further analysis as dkay84_PowerBI said.
Best Regards,
Angelia- uBoatCaptain
v-huizhn-msft This is the result of the formula you suggested. The problem in the matrix is that the filter context is only correct at the Total level. At the other levels because of the filter it gives either budget or minus actual.
Of course what I would like to have is just the total variance showing up once, like it was another column. Suggestions?
