Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
Hi @Anonymous,
You may refer to my solution here.
Hope this helps.
@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.
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...
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.
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.?
@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])
thanks ! I believe you're right...It was a hypothetical question that i didnt think through 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |