Compare 2 visual tables and add condition

[TejasShah]

It'll be really helpful if someone can guide me since I have this case since a long time and couldn't get any related online solution for the same.

I have 2 databases from where I have annual data of last year and daily current data for the current year.

From those dbs, I have created 2 visual tables by filtering the columns as per requirement and calculating the averages for all the 4 types of the product for 5 different tracing stations.

Following are the 2 table visuals which are created:

Annual Avg. data
Cell no	Type1	Type2	Type3	Type4
1	3.13	51.24	488.9	852.76
2	3.74	55.72	502.34	888
3	4.11	52.5	499.2	891.6
4	3.56	53.65	501.67	864.22
5	3.79	57.8	496.4	867.9

Daily Avg. Data
Cell no	Type1	Type2	Type3	Type4
1	3	49.48	490.1	858.5
2	3.81	56.11	506.25	881.93
3	4.52	53	500.9	887.47
4	4.1	56.23	500.12	865
5	3.22	58.31	492.59	866.2

Now, I need to compare the values of Annual Average data for particular type and particular cell no. to it's respective data in the Daily Average data table, with conditions:

for example: If Cell 1, type2 Daily average data is in range of (Cell 1, type2 Annual average data +-10),

then highlight it as Green or else Red and so on.

I tried using the graphically method but the mean itself couldn't be collated in a single graph since both the datas are from different datasources, so average of that particular type displays same average value for all 5 cell types.

Requesting for your support, thank you!

[Anonymous · Accepted solution]

Hi @TejasShah 

Not sure how your source data looks like, so I create a simple demo to show a general and simple practice. 

Assume that your data table is like below. This is a friendly format for Power BI and will make follow-up calculations easier. If your tables are not in this format, you can use Power Query to transform them into this format in advance. 

Add two dimension tables for Cell No and Types. Then link them to Cell No column and Type column in both Annual and Daily tables. This follows the star schema model idea. 

Then create matrix visuals instead table visuals. Use Dimension tables' Cell No and Type column on row and column in both matrices. Create measures to calculate the Avgs for each. 

Then create the following measure for color formatting. In this measure, compare the daily Avg measure with Annual Avg measure. 

Color = 
VAR _DailyAvg = [Daily Avg]
VAR _AnnualAvg = [Annual Avg]
RETURN
IF(_DailyAvg>=_AnnualAvg-10 && _DailyAvg<=_AnnualAvg+10, "Green", "Red")

Hope this would be helpful. If you have any question, feel free to ask and @ me. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!