Hello,
I have one table that contains product information, all unique values:
01-Hammer
02-Screwdriver
03-Wrench
I have two other tables, one that contains customer info
Customer A, 01-Hammer, 5 orders
Customer B, 03-Wrench, 2 orders
Customer C, 03-Wrench, 14 orders
Customer A, 02-Screwdriver, 20 orders
and another that contains external market data :
CityA, 01-Hammer, 80 orders
CityB, 01-Hammer, 35 orders
CityA, 02-Screwdriver, 130 orders
City B, 02-Screwdriver, 70 orders
Both of these tables connect to my first table in a many to one relationship. I pull these last two tables together to get our data vs market data:
01-Hammer, 200 orders (our company), 1500 orders (market)
02-Screwdriver, 100 orders (our company), 1000 orders (market)
03-Wrench, 100 orders (our company), 500 orders (market)
And created a measure for our data and market data to calculate percentage of total products, as well as the difference:
01-Hammer. 200 orders (our company), 50%, 1500 orders (market), 20%, 30%
02-Screwdriver, 100 orders (our company), 25%, 3000 orders (market), 30%, -5%
03-Wrench, 100 orders (our company), 25%, 5000 orders (market), 50%, -25%
The measures I used:
Measure1 = divide(CALCULATE(Count(OurData[ID])),calculate(COUNT(OurData[ID]),all('OurData'[ProductInfo])))Measure2 = divide(CALCULATE(Count(Market[ID])),calculate(COUNT(Market[ID]),all('Market'[ProductInfoField])))Measure3 = Measure1 - Measure2
Now, it pulls the correct values where the two ProductInfo match up, but given it's essentially a many to many relationship, the output is not what we want. In the above example, istead of the 3 rows that I want returned, it returns 9:
01-Hammer, 01-Hammer (correct info)
01-Hammer, 02-Screwdriver
01-Hammer, 03-Wrench
02-Screwdriver, 01-Hammer
02-Screwdriver, 02-Screwdriver (correct info)
02-Screwdriver, 03-Wrench
03-Wrench, 01-Hammer
03-Wrench, 02-Screwdriver
03-Wrench, 03-Wrench (correct info)
So, is there some way I can add a filter so only the matching values are returned? Basically an inner join through the visual's filter box? Thanks for reading!
