Is this possible ?

[RonaldvdH]

Hey guys, i need your help in this one

I have a table and i need a sort of lookupvalue formula based on 2 variabels (column 1 and 2) in table A and then search in table B and sums up the values based on these 2 variables

I already have a summarized table based on project name and week but how do i calculate the 'value' ?

Table A

Project	Week	Value
A	2020-02	summarizes based on Project A ánd 2020-02
A	2020-03	summarizes based on Project A ánd 2020-03
B	2020-02	summarizes based on Project B ánd 2020-02
A	2020-04	summarizes based on Project A ánd 2020-04

Table B

Project	Week	Value
A	2020-02	100
A	2020-02	100
A	2020-02	200
B	2020-03	200
B	2020-04	200
A	2020-03	150

[CheenuSing · Accepted solution]

Hi @RonaldvdH ,

Try this.

Create a calculated column in TableA

Value =

                      Calculate (SUM(TableB[Value]),

                                 FILTER(TableB,

                                        TableB[Project]=TableA[Project] &&

                                         TableB[Week] = TableA[Week] ) )

Cheers

CheenuSing

[PaulDBrown]

@RonaldvdH 

There are a number of ways of doing this. Check out this thread for options:

https://community.powerbi.com/t5/Desktop/Use-Measure-to-Count-How-Many-Times-Value-Appears-in-Anothe... 

[Pragati11]

Hi @RonaldvdH ,

Have you got any relationship between these 2 tables?

If no, can you create a relationship between 2 tables on column "Project Name".

On the report, create a TABLE VISUAL, move "ProjectName" and "Week"  columns from TAble A and "Value" column from Table B with summarisation as SUM.

Please give kudos if this is helpful or Mark it as a Solution if this works! 🙂

Thanks,

Pragati

[RonaldvdH]

@Pragati11 unfortunately that is not possible because one of the 2 columns must have unique values