Create a SUM measure that sums conditionally based on 2 tables

[ElliotPrime]

Hello,

I have a relation between 2 tables on the index columns and I want to create a SUM that would look like this :

CALCULATE(SUM(Table1[Value]) / DISTINCTCOUNT(Table1[Number]), Table1[Number] = Table2[Number])

This command does not work so I wanted to know if it was possible to do something like this.

Thank you

[Anonymous · Accepted solution]

Hi  @ElliotPrime ,

Here are the steps you can follow：

1. Create calculated column.

Average =
var _count=
COUNTX(FILTER(ALL(Table1),'Table1'[WeekNum]=EARLIER('Table2'[WeekNum])),[WeekNum])
var _sum=
SUMX(FILTER(ALL(Table1),'Table1'[WeekNum]=EARLIER('Table2'[WeekNum])),[Value])
return
DIVIDE(_sum,_count)

2. Result:

Best Regards,

Liu Yang

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

[Greg_Deckler]

@ElliotPrime Try:

Measure 4 = 
    VAR __Table = FILTER('Table6',RELATED(Table7[Index]) <> BLANK())
    VAR __Result = DIVIDE( SUMX(__Table, [Value]), COUNTROWS(DISTINCT(__Table)) )
RETURN
    __Result

[ElliotPrime]

Hey @Greg_Deckler 

Thank you for you answer but the result is not quite what I want... Let me illustrate what I want (I made these tables on Excel to be clearer) :

My first table (Table1) looks like this (It is a value associate to a day in a week) :

Table1

I want my second table (Table2) to look like this :

Table2

As you can see, I want to take every values from the Table1 where 'WeekNum' is equal to 'WeekNum' in Table2 and find the average. Here for example, (345 + 324 + 325 + 657 + 865) / 5 = 503,2

My 2 tables already have a relation between them by an index column. Is it possible to not create a measure but a column with this average ?

Thank you