Weighted Average Percentage Calculation - All() fu...

Lannguyen530 · ‎10-23-2018

I am trying to calculated a weighted percentage of a measure in the last 7 days.

The numerator and the denominator of the % are based on the same column. The numerator is open for filters (slicers), and the denominator is open for filters on other slicers except one.

I created a sample dataset to illustrate what my database looks like and what I was looking for in the image here.

On a specific date, one or more divisions are evaluated of their production. For example, on Oct 1, 3 divisions were evaluated of what brands they produced and not produced. Note: at a division, not all brands are evaluated. When an evaluated brand is produced in the day, it is coded as 1 in the produced column, otherwise 0.

I would like to create a visual with slicer of Brand. Note: for illuatration purposes, other columns and slicers were not introduced in this example, but there are other filters that impact the formula.

For the brand selected by the slicer, I would like to produce an Average of Production weighted by the number of plants evaluated on the dates. Please refer to the image for the mechanic that I am looking for.

I have tried the following code, but the All(Data[Brand]) function seem not working. I always got 100% with Brand slicer is filtered to one brand. When I calculated the numerator CALCULATE(sum(Data[Produced)) and the denominator CALCULATE(sum(Data[Produced]),all(Data[Brand])), and the TotalDivision separately on different measures, they show accurate numbers. But when I put them together with the date filter, the % is wrong.

%7BR-T1 = (sumx(CALCULATETABLE(SUMMARIZE(Data,Data[Date],"TotalDivision",DISTINCTCOUNT(Data[Division]),"T1%",CALCULATE(sum(Data[Produced))/CALCULATE(sum(Data[Produced]),all(Data[Brand])),DATESINPERIOD(Data[Date],CALCULATE(max(Data[LoadDate]))-1,-7,DAY)),[TotalDivision]*[T1%]))/(sumx(CALCULATETABLE(SUMMARIZE(Data,Data[Date],"TotalDivision",DISTINCTCOUNT(Data[Division])),DATESINPERIOD(Data[Date],CALCULATE(max(Data[Date]))-1,-7,DAY)),[TotalDivision]))

Any help would be greatly appreciated. Thank you

v-juanli-msft · ‎10-25-2018

Hi @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

Lannguyen530 · ‎10-25-2018

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

Lannguyen530 · ‎10-23-2018