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?
