compare between 2 values

[RMV]

 Hi all,

I need help for my calculation below.

I have 2 tables as follow

Table #1

Date          Region         Amount1

1-Jan-17     A                   1000

1-Jan-17     B                     500

1-Jan-17     C                   2000

2-Jan-17     A                   1100

2-Jan-17     B                     700

3-Jan-17     A                   1200

3-Jan-17     C                   2500

Table #2

Region       Amount2

A                   1000

B                     750

C                   2000

Then, I need to compare between Average of Amount1 per Region with Amount2. The illustration of the comparison is as follow

Region      Average of Amount 1              Amount2

A               1100 ((1000+1100+1200)/3)       1000

B                 600 ((500+750)/2)                       750

C                2250 ((2000+2500)/2)                 2250

At the end, I need to sum up the higher figure between these two figures, i.e. 

Region        Amount1/2

A                   1100 (from Average of Amount1)

B                     750 (Amount2)

C                   2250 (from Amount2)

Total = 4100 -> the figure I need

I try to apply this formula below in Table1, but it does not work out

Total Amount1/2 = SUMX(VALUES(Table1[Region]),IF([Average of Amount1] > Table2[Amount2], [Average of Amount1], Table2[Amount2))

While Average of Amount1 is a measure column:

Average of Amount1 = CALCULATE(SUM[Amount1],FILTER(Table1,AND(MIN(Table1[Date]),MAX(Table1[Date])))) / CALCULATE(DISTINCTCOUNT(Table1[Date]),FILTER(Table1,AND(MIN(Table1[Date]),MAX(Table1[Date]))))

I'm not sure I 'm using the best formula for Average of Amount1 either, but it currently results the correct figure for average of amount1 per region. However, Total Amount1/2 I have now is definitely incorrect.

Appreciate any help. Thank you.

[v-huizhn-msft · Accepted solution]

Hi @RMV,

I try to reproduce your scenario and get expected result.

1. Create relationship between the two tables.



2. Create calculated column in Table#2 using the formula.

max Average of Amount 1 = IF( CALCULATE( AVERAGE('Table#1'[Amount1]),USERELATIONSHIP('Table#1'[Region],'Table#2'[Region]))>'Table#2'[Amount2],CALCULATE( AVERAGE('Table#1'[Amount1]),USERELATIONSHIP('Table#1'[Region],'Table#2'[Region])),'Table#2'[Amount2])

3. Create a measure to get the desired result. Create a card visual dsiplay it.

Total Amount1/2 = SUM('Table#2'[max Average of Amount 1])

Best Regards,
Angelia