Forum Discussion
All() function not working in Weighted Average Percentage
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