Need help with DAX

[Anonymous]

New to Power BI and trying to solve a query. I have two tables and need a calculated value based on name and month

Table A                                                                                     

Date	Reviewer	Preparer	Summary
10-1-22	Paul	Nancy	3
10-12-22	Nancy	John	4
10-15-22	Paul	Adam	2
10-9-22	John	Paul	5

Here from Table A, need Sum of summary column from both Preparer and Reviewer. That is value of Paul will be 10(3+2+5) 

Nancy - 7, John - 9, Adam - 2

Table B

Month	Name	Grade
Oct	Paul	1
Oct	John	2
Oct	Paul	1

Final productivity will be sum of Table A and Table B

Paul = 10+(1+1)

John = 9 + 2 and so on for each name

[v-yinliw-msft]

Hi @Anonymous ,

You can try this method:

New a table:

Result = SELECTCOLUMNS('TableA',"Name",'TableA'[Preparer])

 Then in the tableA create a measure:

ResultA =
CALCULATE (
    SUM ( TableA[Summary] ),
    FILTER (
        'TableA',
        'TableA'[Reviewer] = SELECTEDVALUE ( Result[Name] )
            || 'TableA'[Preparer] = SELECTEDVALUE ( Result[Name] )
    )
)

In the tableB create a measure:

ResultB =
CALCULATE (
    SUM ( TableB[Grade] ),
    FILTER ( 'TableB', 'TableB'[Name] = SELECTEDVALUE ( Result[Name] ) )
)

In the result table, create a measure:

Result = [ResultA] + [ResultB]

The result is:

Hope this helps you.

Here is my PBIX file.

Best Regards,

Community Support Team _Yinliw

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

[Anonymous]

Hi @v-yinliw-msft ,

Thanks for the solution. I have data for entire year and the result is displayed as sum of all months. I have created slicer for month and need the corresponding month's data. I have created date dimension table from Table A , Table B also has month column. Any suggestions?