Forum Discussion
Helper ISummarization of weighted average
Helper I
Hello Lydia,
Thank you for the reply
US_Material[GTIN] is renamed as Dmd Unit in the table and US_Material[Material Text] is renamed as Material Name
I want the weighted to be base hist/sum(basehist) of all the US_Material[Product Line] selected in the slicer/filter . This is where I'm facing the problem. The formula works fune if one Product line is selected. when multiple product lines are selected the DAX formula Im using is giving the weight for that Product line and not all of the product lines:
The total weight for weighted average calculation is considering 27,108 for lines with product line 003 and 132,307 for lines with product line 006. I want all of the lines to use 159,415 (total of both the sales) for all the lines in this case. it looks like the row context of the product line is coming in picture in this case. How can I not consider the row context of product line but still consider the filter/slicer in the calculation.
US_Material [Product Line] | US_Material [GTIN] | US_Material [Material Text] | BaseHist | Terras Stored Forecast | TerraAccuracyitemlevel | Weighted | Weighted Terra Accuracy | Terra Accuracy | TerraAccuracyItemLevel =MAX(0,IF(D2=0,0,1-(ABS(D2-E2)/D2))) | Weighted=D2/D$23 | Weighted terra Accuracy = K2*J2 |
| 6 | 33200865278 | AHPLD AHOC FRSH SCNT 40/215LD BKT DIS | 175 | 174.28 | 99.59% | 0 | 0.11% | 99.59% | 99.59% | 0.00 | 0.11% |
| 6 | 10033200060229 | AHPLD AHOC TROPICAL BURST 4/80LD | 7671 | 7721.48 | 99.34% | 0.05 | 4.78% | 99.34% | 99.34% | 0.05 | 4.78% |
| 6 | 10033200065101 | AHPLD AHOC FRSH SCNT 3/130L TALL | 10630 | 12117.43 | 86.01% | 0.07 | 5.74% | 86.01% | 86.01% | 0.07 | 5.74% |
| 6 | 10033200065132 | AHPLD 4/80LD AHOC FRSH SCNT COMPACTION | 12951 | 15549.51 | 79.94% | 0.08 | 6.49% | 79.94% | 79.94% | 0.08 | 6.49% |
| 6 | 10033200065149 | AHPLD CLN BURST 4/100LD | 0 | 4.03 | 0 | 0.00% | 0.00% | 0.00 | 0.00% | ||
| 6 | 10033200065200 | AHPLD 5/58L FREE | 1591 | 1585.88 | 99.68% | 0.01 | 0.99% | 99.68% | 99.68% | 0.01 | 0.99% |
| 6 | 10033200065217 | AHPLD CLN BURST 3/160L TALL | 3020 | 2987.68 | 98.93% | 0.02 | 1.87% | 98.93% | 98.93% | 0.02 | 1.87% |
| 6 | 10033200065231 | AHPLD CLEAN BURST 5/58LD | 0 | 2.79 | 0 | 0.00% | 0.00% | 0.00 | 0.00% | ||
| 6 | 10033200065248 | AHPLD 4/100L FREE | 1842 | 1793.2 | 97.35% | 0.01 | 1.12% | 97.35% | 97.35% | 0.01 | 1.12% |
| 6 | 10033200065255 | AHPLD AHOC FRSH SCNT 5/45LD | 2295 | 2192.78 | 95.55% | 0.01 | 1.38% | 95.55% | 95.55% | 0.01 | 1.38% |
| 6 | 10033200941726 | AHPLD ALPINE CLN 3/155L | 12695 | 13289.98 | 95.31% | 0.08 | 7.59% | 95.31% | 95.31% | 0.08 | 7.59% |
| 6 | 10033200941733 | AHPLD AHOC FRSH SCNT 3/115LD | 14575 | 14426.92 | 98.98% | 0.09 | 9.05% | 98.98% | 98.98% | 0.09 | 9.05% |
| 6 | 10033200971952 | AHPLD CMPLT CRISP CLEAN 1/290LD BKT | 57120 | 33236.76 | 58.19% | 0.36 | 20.85% | 58.19% | 58.19% | 0.36 | 20.85% |
| 6 | 10033200972003 | AHPLD ALPINE CLN 2/185LD BKT | 2093 | 2193.69 | 95.19% | 0.01 | 1.25% | 95.19% | 95.19% | 0.01 | 1.25% |
| 6 | 10033200972775 | AHPLD CLN BURST 5/3.58LB/40LD | 5649 | 5840.01 | 96.62% | 0.04 | 3.42% | 96.62% | 96.62% | 0.04 | 3.42% |
| 3 | 33200032601 | AHSWS 6/55OZ | 19150 | 20556.07 | 92.66% | 0.12 | 11.13% | 92.66% | 92.66% | 0.12 | 11.13% |
| 3 | 10033200030208 | AHSWS 12/55OZ | 6662 | 5633.62 | 84.56% | 0.04 | 3.53% | 84.56% | 84.56% | 0.04 | 3.53% |
| 3 | 10065333003309 | AHSWS 6/3KG | 1296 | 1327.75 | 97.55% | 0.01 | 0.79% | 97.55% | 97.55% | 0.01 | 0.79% |
| 6 | 10033200065279 | AHPLD AHOC FRESH SCENT 1/215LD BKT | 0 | 0 | 0 | 0.00% | 0.00% | 0.00 | 0.00% | ||
| 6 | 10033200065132 | AHPLD AHOC FRSH SCNT 4/80LD COMPCTN IRC | 0 | 0 | 0.00% | 0.00% | 0.00 | 0.00% | |||
| 6 | 10033200942211 | AHPLD AHOC ISLAND RAIN 3/8.87LB/115LD | 0 | 0 | 0.00% | 0.00% | 0.00 | 0.00% | |||
| PowerBI Totals | 159415 | 140634 | 88.22 | 1 | 168.8 | 168.80% | |||||
| Totals | 159415 | 80.00% | 80.00% | 1.00 | 80.00% |
Helper I