Forum Discussion
Composite weight using values from 2 tables.
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...
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.
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?
Hi Anonymous,
Has your problem been solved? Did my solution work? If not, then please let me know the problem with my solution.