Calculate Average Data based on Filters

[Anonymous]

Hi there,

I am trying to create two sets of data for comparison, one based on a single table the other based on values from two tables.
These tables are imported via SQL Database through power query. I have no direct access to modify / create tables in the SQL databases so hoping to do this in Power BI.

Table 1 - Predictions
Predicted condition over a range of years (full dataset has a range of 20 years)

predictions
UIDX	scenario	yr	condition
1|1	0	2020	8.41
1|1	0	2021	9.92
1|1	0	2022	9.9
1|2	0	2020	8.85
1|2	0	2021	9.92
1|2	0	2022	9.9
2|1	0	2020	8.98
2|1	0	2021	9.14
2|1	0	2022	8.97
3|1	0	2020	6.83
3|1	0	2021	6.48
3|1	0	2022	5.87
4|1	0	2020	9.46
4|1	0	2021	-0.47
4|1	0	2022	-1.05
5|1	0	2020	7.27
5|1	0	2021	7.53
5|1	0	2022	7.26
6|1	0	2020	9.33
6|1	0	2021	9.26
6|1	0	2022	9.16
6|2	0	2020	9.22
6|2	0	2021	8.97
6|2	0	2022	8.82

Table 2 - Area
Table that stores standardised area across the database.

area
UIDX	area
1|1	1287
1|2	2485
2|1	1625.9
3|1	2448.6
4|1	3300
5|1	2492.9
6|1	5368
6|2	4048

The tables are in a relationship based on the UIDX field.

I would like to calculate the following:
Query 1 - Average 'condition' - result to be displayed in a card using the following criteria:

• Where Scenario = 0
• And yr = 2021

e.g. cond_avg = CALCULATE(AVERAGE(predictions[condition]),FILTER(predictions,predictions[yr] = "2021"))

I used a New Measure to try and calculate Query 1. It initially appeared fine but when I add the results to a card I get the following error, interestingly the columns are both format Decimal Number.:
Calculation error in measure 'predictions'[cond_avg]: DAX comparison operations do not support comparing values of type Number with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

Query 2 - Average condition x Area - display average condition multiplied by area in a card based on the following criteria:

• Where Scenario = 0
• And yr = 2021

I have tried to get a result from joining tables thought have been unsuccessful. I am trying to replicate the following (assume the query 1 will need to be integrated?):
Sum(predictions[condition]*area[area])/sum(area[area])

Is anyone able to assist? Thanks in advance!

[Anonymous · Accepted solution]

Hi @v-zhangti 

Thank you for your quick response.

Query 1 - results look as expected, terrific.

Query 2 - the core data is a much larger sample set but comparing your query against sample data there is a discrepency. The methodology you suggested looks sound though I think there may be a mismatch with UIDX so have forwarded off to my engineer for review.

Will update when I have a response from them.

Cheers

Matt_au

[v-zhangti]

Hi, @Anonymous 

For Query 1, you can try the following methods.

cond_avg =
CALCULATE (
    AVERAGE ( Predictions[condition] ),
    FILTER ( ALL ( Predictions ), [yr] = 2021 && [scenario] = 0 )
)

In response to Query 2, I don't know if I understand it correctly, please confirm if it is the result you expect.

Add calculated columns:

Area =
IF (
    [scenario] = 0
        && [yr] = 2021,
    LOOKUPVALUE ( Area[area], Area[UIDX], [UIDX] ),
    BLANK ()
)

Column = [condition]*[Area]

Measure:

Query 2 =
DIVIDE ( SUM ( Predictions[Column] ), SUM ( Area[area] ) )

If the method I provided above can't solve your problem, what's your expected result? Could you please provide more details for it?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you for your help @v-zhangti checked with our engineer and he was happy with the results.

Cheers

Matt_au