Forum Discussion
All() function not working in Weighted Average Percentage
Community SupportHi Lannguyen530
Let me know more about the following confusion.
1."Number of Plants Evaluated" column show the wrong values in your picture, right?
According to the following statement:
For example, on Oct 1, 3 divisions were evaluated of what brands they produced and not produced.
For the right table in your picture, after select Brand A, on Oct 1, "Number of Plants Evaluated" column should show 2, right?
2.produce an Average of Production weighted by the number of plants evaluated on the dates.
Could you give an example how to calculate this by a mathematical formula?
and how to calculate Average of Production?
Best Regards
Maggie
Thank you v-juanli-msft for your reply,
To your questions,
1. You are right. After A is selected, the Number of Plants Evaluated should be 2.
2. I was not calculating Average of Production but rather % of Production and then weight the percentages by Number of Plant Evaluated. The % is calculated as sum of Produced column, filtered by brand and by Date, divided by the total evaluations of the day. For example, with A brand filted by the slicer, on Oct 1, brand A was evaluated at 2 plants but only 1 plant produced the product A (row D2 in the picture). So the % for brand A on Oct is (1+0)/8. It should be something like CALCULATE(sum(Data[Produced))/CALCULATE(sum(Data[Produced]),all(Data[Brand])) - please refer to my full formula in my original message. My formula was trying to leave the numerator open so that it is impacted by slicer (slicer of Brand in this case so that brand A is filtered), and the denominator clear the filter on the Brand but keep other slicer filters in effect. However, the challenge was that the All(Data[Brand]) did not work, yielding a result of 100% all the time.
Hope that answer your questions. Thank you