Composite weight using values from 2 tables.

[Anonymous]

Hi 

I have a table in which I have weights assigned to a Country-Product group. For ex.

Table A:

country	Product	Weight
Australia	A	1
Australia	X2	0.4388
Brazil	X1	0.9763
Brazil	G	1
Canada	H	1
Canada	X1	0.456132
Canada	J	1
Canada	X2	0.337829
Canada	X3	0.206039
Canada	P	1
Canada	Q	1

I have another table where we collect some data on survey response in each country for products. A sample table looks like this.

Table B:

ResponseID	Country	Product	Response Recieved?
1	Australia	A	Y
2	Brazil	X2	Y
3	Canada	X1	Y
4	Australia	X3	Y
5	Brazil	H	Y
6	Canada	X1	N
7	Australia	J	Y
8	Brazil	X2	Y
9	Canada	X3	Y
10	Australia	P	Y
11	Brazil	Q	N
12	Canada	A	Y
13	Australia	X2	Y
14	Brazil	X1	Y
15	Canada	G	Y
16	Australia	H	Y
17	Brazil	X1	Y
18	Canada	J	Y
19	Australia	X2	Y
20	Brazil	X3	Y

Now I need to calcuate a composite weight using the following formula 

composite weight = Weight of the product in the country (from table A) / proportion of responses recieved for that product in a country

So, for ex. the composite weight for X2 product in Australia would be calculated as

weight of X2 in Australia = 0.4388 (from Table A)

no. of response recieved for X2 in australia = 2 (rows 13 and 19 in table B)

Total no. of responses recieved in Australia for all products = 7 ('Y' against Australia from Table B)

Proportion of X2 response to all responses in Australia = 2/7 = 0.2857

composite weight = 0.4388/0.2857 = 1.535877

Now, I need to calculate the composite weights only for Products starting with X i.e. X1, X2 and X3. For all other products, the composite weight should be 1

Also, there will be slicers in the report. So this calculation needs to be dynamic.

I'm struggling to get this done. So seeking help from the members here.

Please help.

Thanks

Anupam

[Ashish_Mathur]

Hi @Anonymous,

You may refer to my solution here.

Hope this helps.

[Anonymous]

---

@Ashish_Mathur wrote:

Hi @Anonymous,

You may refer to my solution here.

Hope this helps.

---

Thank you ! this is great. Although I see the measure doing what was required to be done, i'm unable to figure out how did you make sure that only 'X' products are getting weighted and nothing else?

[Ashish_Mathur]

Hi @Anonymous,

Has your problem been solved?  Did my solution work?  If not, then please let me know the problem with my solution.

[erik_tarnvik]

It is this portion:

...
VAR X = LEFT(MAX(TableA[Product]), 1) = "X"
RETURN
    IF(X, DIVIDE(AWeight, Props),1)

you could rewrite this as

    IF(LEFT(MAX(TableA[Product]), 1) = "X", DIVIDE(AWeight, Props),1)

The IF function evalutes wether the first letter of TableA[Product] is equal to "X" and returns the ratio if it is and 1 otherwise. The LEFT function requires a scalar value and not a column, that is why I use the MAX function to extract a scalar value. As I commented, this measure assumes that it is evaluated in a filter context where TableA[Product] only has one value. So, you could have used MIN just as well as MAX as the result is the same for a column with only one value.

[Anonymous]

---

@erik_tarnvik wrote:

It is this portion:

...
VAR X = LEFT(MAX(TableA[Product]), 1) = "X"
RETURN
    IF(X, DIVIDE(AWeight, Props),1)

you could rewrite this as

    IF(LEFT(MAX(TableA[Product]), 1) = "X", DIVIDE(AWeight, Props),1)

The IF function evalutes wether the first letter of TableA[Product] is equal to "X" and returns the ratio if it is and 1 otherwise. The LEFT function requires a scalar value and not a column, that is why I use the MAX function to extract a scalar value. As I commented, this measure assumes that it is evaluated in a filter context where TableA[Product] only has one value. So, you could have used MIN just as well as MAX as the result is the same for a column with only one value.

---

thanks Erik. my question was meant for Ashish. I have now edited my post. noob mistake.

[erik_tarnvik]

No problem. BTW the answer is that @Ashish_Mathur filtered the visual on products:

[Anonymous]

---

@erik_tarnvik wrote:

No problem. BTW the answer is that @Ashish_Mathur filtered the visual on products:

---

ah, got it !

well that kind-of circumvents the original problem as i need the measure to be used in futher calculations...

[erik_tarnvik · Accepted solution]

Somewhat complex challenge you got there. Try this measure, I am not sure I got all your conditions right, if not let me know and I'll rework:

CompositeWeight = 
VAR AWeight = SUM(TableA[Weight])
VAR ACountry = MAX(TableA[Country])
VAR Prod = MAX(TableA[Product])
VAR NoResponsesCountry = COUNTROWS(FILTER(TableB, TableB[Product] = Prod && TableB[Response Recieved?] = "Y" && ACountry = TableB[Country]))
VAR NoResponses = COUNTROWS(FILTER(TableB, TableB[Response Recieved?] = "Y" && ACountry = TableB[Country]))
VAR Props = DIVIDE(NoResponsesCountry, NoResponses)
VAR X = LEFT(MAX(TableA[Product]), 1) = "X"
RETURN
    IF(X, DIVIDE(AWeight, Props),1)

I am assuming here that you would use this in a visual with at least Product and Country as row contexts. I'm using a lot of variables in order to explain, no need for all of that in your work product of course.

[Anonymous]

thank you Erik. your function would do the job.. Just one question though, what if the country or the product slicers aren't in the dashboard. how will this function change.?

[erik_tarnvik]

---

@Anonymous wrote:

thank you Erik. your function would do the job.. Just one question though, what if the country or the product slicers aren't in the dashboard. how will this function change.?

---

If you think about the calculation as you define it, it would not have a defined value unless the country and product context is known when calculating the composite weight. You would have to define what this means.

One possibility would be to create a calculated table with Country, Product and Composite Weight, but whether that would be useful I can't really tell without knowing more about what you want to accomplish exactly. But in case it would be, you can do this by clicking New Table and enter the following:

CWTable = SUMMARIZE(TableA, TableA[Country], TableA[Product], "CW", [CompositeWeight])

[Anonymous]

thanks ! I believe you're right...It was a hypothetical question that i didnt think through 🙂