Unable to create calculated column from measure

[NathanND]

Hi guys, I'm relatively new to PowerBI so excuse me if this question turns out too trivial but I've tried and I simply could not manage to find the solution.
To give you some background, I have two data sets for energy produced by a project, one from the grid operator and one from our own monitoring. These are comparable on a monthly basis and I'm checking the deviation between the two recorded values and categorising them and so on.
The picture below shows the relationship of the different tables used.

Table 1 (Park Details) has the Plant details from the monitoring system we use including the name
Table 2 (Park WaterFall) has the monthly energy production values for the plant taken from the monitoring platform and the relation netween table 1 and 2 is the plant name (many to one)
Table 3 (msdyn_customerassets) is data from our microsoft dynamics platform which has the park details as well and the park name is used to link it to table 1 (one to one)
Table 4 (ff_certiqs_table) has the monthly energy values stored from the grid operator and is linked to table 3 by an ID unique to the plant and common to both tables (many to one)

I made the following measure to calculate the difference of the related plants:

Energy Diff = CALCULATE( SUM('Park WaterFall'[Energy])-SUM(ff_certiqs_table[CertiQ Energy]))

and another measure to calculate the percentage difference, but the issue faced is basically the sam, that I cannot do so because they are not directly related.

While I am able to show the deviation and categorise them in a table visual to study them, I would also like to have cards where I can filter the different categories (to give count of number the of cases with 5 to 10 % deviation or number of cases where deviation is more thatn 10%, etc).
Furthermore I would like to look at the underlying data in the table visual and would like to make a slicer with the different percentage deviation categories but this is not possible while using a measure.

Therefore, I tried to create a calculated column which I can place in either table 2 or table 4 but since the relationships are indirect I could not figure out how to do it.

I tried use an inactive direct relationship by making a one to one relationship (between table 2 & 4) by concatenating the park name and the date on which the data is to be compared, but it gives rise to a many to many relationship which I think should not be the case (might caused by some entries in either plaform not existing in the other).

Does any one know how can make a calculated column to compare the values from table 2 and 4 based on the measure I created or do you have any better solutions?

[amitchandak]

@NathanND , you can not create a column on measure.

Using the row context you need to use measure.

Like in this example date diff is done at order number level

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Differ...

[NathanND]

Hi @amitchandak ,
Thanks for the quick reply! However, I think I may have been misunderstood. I'm not trying to create a column based on a measure, rather I am trying to create a column instead of a measure. I understand that this may be possible with tables that are directly related by using the related function, but since the tables are indirectly related I wanted to know if this is possible.

[v-yiruan-msft]

Hi @NathanND ,

You can refer the solution in the following threads to get it:

Calculate column with 2 tables without relation

Sperate tables calculation

Create a Calculated Column from Different Tables

Best Regards

Rena