Self-join to create a measure, columns virtual

[uBoatCaptain]

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

[v-huizhn-msft]

Hi @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?

[dkay84_PowerBI]

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?

[uBoatCaptain]

@dkay84_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_PowerBI]

Can 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?

[uBoatCaptain]

@dkay84_PowerBI I think you are asking me if I can import the data and convert the virtual columns to actual columns when I do it. I was not aware the Query Editor can do this (which is useful to know), but I know how to do it with SQL anyway. It is just not what I want to do.

My question is really about whether I can model the data in PowerBI the way it comes from the source system. I can leave the columns "vitual" as they are in the source system, or I can pivot them to actual columns as they will almost always be presented. But the latter has some drawbacks I already mentioned and is not my preferred way to go.

I think there should be a way to keep the columns "virtual", as in this case the columns usually correspond to a time period, so they are similar to a date on a sales record. Clearly there are times when users would want to compare sales during one period to sales in a different period. Or expenses in one period to another period. You would not pivot each period to a separate column to do this. Could the DAX time intelligence functions be the answer I am looking for?

[v-huizhn-msft]

Hi @uBoatCaptain,

If we create a measure, and select it as value field in Martix visual. It will display the corresponding result in each column field. It's unable to only show result in Total column.

Thanks,
Anglie