Forum Discussion
Composite weight using values from 2 tables.
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?
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.
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 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...