Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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...
Need Help
  • erik_tarnvik's avatar
    erik_tarnvik
    8 years ago

    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's avatar
Anonymous
Not applicable
8 years ago

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's avatar
erik_tarnvik
Solution Specialist
8 years ago
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's avatar
    Anonymous
    Not applicable
    8 years ago

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