Retrieving data from different tables based on IF rules and matching table

[Anonymous]

Hi there

I am trying to aggregate 3 different data sources for reporting. All 3 sources contain revenue data for the same clients (clients may or may not exist in all sources) but depending on where revenue exists for each client a different rule has to be applied. This is a sample of what the source tables look like:

Table A
Name	Month	Revenue
Thorntons	Jan-18	7,500
Thorntons	Feb-18	2,000
Nike	Jan-18	3,000
Travelodge	Jan-18	2,100
Travelodge	Feb-18	3,000
Asics	Jan-18	2,700
Table B
Name	Month	Revenue
Thorntons UK	Jan-18	5,000
Thorntons UK	Feb-18	3,200
Nike Ltd		4,500
Table C
Name	Month	Revenue
Thorntons_Affiliate	Jan-18	5,000
www.travelodge.co.uk	Jan-18	1,500
www.travelodge.co.uk	Feb-18	3,100

I also have a mapping table to match the client names across different data sources and to decide which revenue to pick up:

Reporting ID	Name_A	Name_B	Name_C	Identifier	Rule
73	Thorntons	Thorntons UK	Thorntons_Affiliate	A, B, C	B + C
39	Nike	Nike Ltd		A, B	B
12	Travelodge		www.travelodge.co.uk	A, C	C
61	Asics			A	A

As you can see if a client has revenue in tables B or C, then these need to be chosen instead of A, but if they're in both B and C, the revenues have to be added, else take revenue from A. The source data will have monthly data so I won't be able to just add a calculated column to this mapping table using IF loops.

Is there a way to create a measure or a revenue table that would be able to pull in the revenue from the right tables for each month based on the rules in the mapping table?

For the above, the resulting values should be:

Name	Month	Revenue
Thorntons	Jan-18	10,000
Thorntons	Feb-18	3,200
Nike	Jan-18	4,500
Travelodge	Jan-18	1,500
Travelodge	Feb-18	3,100
Asics	Jan-18	2,700

Thanks!
 

[v-lili6-msft]

hi, @Anonymous

     After my research, The main difficulty is that there are blank values in the column Month of table2 or table3.

and you can do these as below:

add a column that whether the name exists in table 2

table2name = IF(
      SUMX(Table2,
           FIND(
                UPPER(Table1[Name]),
                UPPER(Table2[Name])
                ,,0
               )
          ) > 0,
      Table1[Name]
   )

add a column that whether the name exists in table 3

table3name = IF(
      SUMX(Table3,
           FIND(
                UPPER(Table1[Name]),
                UPPER(Table3[Name])
                ,,0
               )
          ) > 0,
      Table1[Name]
   )

Add a column that exists similar name and the same month total revenue in table 2

table2 Revenue = CALCULATE(SUM(Table2[Revenue]),FILTER(Table2,Table2[Month]=Table1[Month]&& SUMX(Table2,
           FIND(
                UPPER(Table1[Name]),
                UPPER(Table2[Name])
                ,,0
               )
          ) > 0&&SEARCH(UPPER(Table1[Name]),UPPER(Table2[Name]),1,0)>0))

Add a column that exists similar name and the same month total revenue in table 3

table3 Revenue = CALCULATE(SUM(Table3[Revenue]),FILTER(Table3,Table3[Month]=Table1[Month]&& SUMX(Table3,
           FIND(
                UPPER(Table1[Name]),
                UPPER(Table3[Name])
                ,,0
               )
          ) > 0&&SEARCH(UPPER(Table1[Name]),UPPER(Table3[Name]),1,0)>0))

Add a column that exists similar name but no month total revenue in table 2

table2 no month Revenue = CALCULATE(SUM(Table2[Revenue]),FILTER(Table2, SUMX(Table2,
           FIND(
                UPPER(Table1[Name]),
                UPPER(Table2[Name])
                ,,0
               )
          ) > 0&&SEARCH(UPPER(Table1[Name]),UPPER(Table2[Name]),1,0)>0))

Add a column that exists similar name but no month total revenue in table 3

table3 no month Revenue = CALCULATE(SUM(Table3[Revenue]),FILTER(Table3, SUMX(Table3,
           FIND(
                UPPER(Table1[Name]),
                UPPER(Table3[Name])
                ,,0
               )
          ) > 0&&SEARCH(UPPER(Table1[Name]),UPPER(Table3[Name]),1,0)>0))

Now summarize data and get the result:

table2total = IF(ISBLANK(Table1[table2name])=FALSE()&&ISBLANK(Table1[table2 Revenue])=FALSE(),Table1[table2 Revenue],IF(ISBLANK(Table1[table2name])=FALSE()&&ISBLANK(Table1[table2 Revenue]),Table1[table2 no month Revenue]))

table3total = IF(ISBLANK(Table1[table3name])=FALSE()&&ISBLANK(Table1[table3 Revenue])=FALSE(),Table1[table3 Revenue],IF(ISBLANK(Table1[table3name])=FALSE()&&ISBLANK(Table1[table3 Revenue]),Table1[table3 no month Revenue]))

Result = IF(ISBLANK(Table1[table2name])=FALSE()&&ISBLANK(Table1[table3name])=FALSE(),Table1[table2total]+Table1[table3total],IF(ISBLANK(Table1[table2name])=FALSE()&&ISBLANK(Table1[table3name]),Table1[table2total],IF(ISBLANK(Table1[table2name])=ISBLANK(Table1[table3name])=FALSE(),Table1[table3total],IF(ISBLANK(Table1[table2name])&&ISBLANK(Table1[table3name]),Table1[Revenue]))))

Result:

here is pbix, please try it.

https://www.dropbox.com/s/edtpxxerps2dwo9/etrieving%20data%20from%20different%20tables%20based%20on%20IF%20rules%20and%20matching%20table.pbix?dl=0

Best Regards,

Lin

[Anonymous]

Hi Lin

Thanks so much for getting back to me on this. This is really useful for getting in all the revenue amounts into one table but I had a couple of questions around whether it's possible to amend some of the formulas:

• Table2 name and Table3 name should be the respective names in the tables but this formula uses the names in table 1, is it possible to pull in the other names into this table? (ie for Thorntons it's Thorntons UK and Thorntons_Affiliate)
• Is it possible to create this table dynamically - ie if I add new month to the other tables (add March-18 to Table 2), will it automatically create a new row in this table (have 3 Thorntons rows)?

Thanks

Dhushy