Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Line | actual qty | Total | Week | Col_NVL |
Line01 | 25725 | 18 | WK14 | 11.4703936289298 |
Line01 | 7700 | 18 | WK14 | 11.4703936289298 |
Line01 | 9000 | 18 | WK14 | 11.4703936289298 |
Fomular:
Col_NVL = CALCULATE( averagex( 'NVL',sumx('NVL','NVL'[actual qty]*'NVL'[Total])/sum(NVL[actual qty])),FILTER(ALLEXCEPT('NVL',NVL[Line.]),'NVL'[Week]<=max('NVL'[Week])))
it should be: (25725*18+7700*18+9000*18)/(25725+7700+9000) = 18 (note: total sometimes same, sometimes not)
Solved! Go to Solution.
Try like
Col_NVL = CALCULATE(
divide(sumx('NVL','NVL'[actual qty]*'NVL'[Total]),sum(NVL[actual qty])),ALLEXCEPT('NVL',NVL[Line.]),FILTER(,'NVL'[Week]<=max('NVL'[Week])))
move all expect outside filter.
Thank you all for the suggestions, till now, the most close formula is still:
Since data is big, don't know how to upload, so here just take one Line data as example.
Workshop | Line No. | actual qty | Total | Week | Year | WeekNum |
W | Line12Q | 26208 | 15.8 | WK52 | 2019 | 201952 |
W | Line12Q | 26208 | 15.8 | WK51 | 2019 | 201951 |
W | Line12Q | 26208 | 15.8 | WK50 | 2019 | 201950 |
W | Line12Q | 26208 | 15.8 | WK49 | 2019 | 201949 |
W | Line12Q | 25725 | 18 | WK14 | 2020 | 202014 |
W | Line12Q | 25725 | 18 | WK13 | 2020 | 202013 |
W | Line12Q | 25725 | 18 | WK12 | 2020 | 202012 |
W | Line12Q | 25725 | 18 | WK11 | 2020 | 202011 |
W | Line12Q | 25725 | 18 | WK10 | 2020 | 202010 |
W | Line12Q | 25725 | 18 | WK09 | 2020 | 202009 |
W | Line12Q | 25725 | 18 | WK08 | 2020 | 202008 |
W | Line12Q | 25725 | 18 | WK03 | 2020 | 202003 |
W | Line12Q | 25725 | 18 | WK01 | 2020 | 202001 |
W | Line12Q | 94044 | 3.7 | WK52 | 2019 | 201952 |
W | Line12Q | 94044 | 3.7 | WK51 | 2019 | 201951 |
W | Line12Q | 94044 | 3.7 | WK50 | 2019 | 201950 |
W | Line12Q | 94044 | 3.7 | WK49 | 2019 | 201949 |
W | Line12Q | 7700 | 18 | WK14 | 2020 | 202014 |
W | Line12Q | 9000 | 18 | WK14 | 2020 | 202014 |
W | Line12Q | 7700 | 18 | WK13 | 2020 | 202013 |
W | Line12Q | 9000 | 18 | WK13 | 2020 | 202013 |
W | Line12Q | 7700 | 18 | WK12 | 2020 | 202012 |
W | Line12Q | 9000 | 18 | WK12 | 2020 | 202012 |
W | Line12Q | 7700 | 18 | WK11 | 2020 | 202011 |
W | Line12Q | 9000 | 18 | WK11 | 2020 | 202011 |
W | Line12Q | 7700 | 18 | WK10 | 2020 | 202010 |
W | Line12Q | 9000 | 18 | WK10 | 2020 | 202010 |
W | Line12Q | 7700 | 18 | WK09 | 2020 | 202009 |
W | Line12Q | 9000 | 18 | WK09 | 2020 | 202009 |
W | Line12Q | 7700 | 18 | WK08 | 2020 | 202008 |
W | Line12Q | 9000 | 18 | WK08 | 2020 | 202008 |
W | Line12Q | 7700 | 18 | WK03 | 2020 | 202003 |
W | Line12Q | 9000 | 18 | WK03 | 2020 | 202003 |
W | Line12Q | 7700 | 18 | WK01 | 2020 | 202001 |
W | Line12Q | 9000 | 18 | WK01 | 2020 | 202001 |
W | Line12Q | 12762 | 11 | WK52 | 2019 | 201952 |
W | Line12Q | 12762 | 11 | WK51 | 2019 | 201951 |
W | Line12Q | 12762 | 11 | WK50 | 2019 | 201950 |
W | Line12Q | 12762 | 11 | WK49 | 2019 | 201949 |
I found the root cause here:
Average for sub-level result is not equal to average for whole raw data. For example:
Group Sub-level Data
A A1 2
A A1 2.2
A A2 2.1
B B1 2.7
Average A = average (A1,A2,B1) Does not the same with
Average A = average (data)
Thank you all!!!
Hi @Anonymous ,
I have added a row to enrich your data,as you see below:
Then I modify your measure to the following one:
Measure =
var a=CALCULATE(SUMX('Table','Table'[actual qty ]*'Table'[Total]),ALLEXCEPT('Table','Table'[Line]))
var b=CALCULATE(SUMX('Table','Table'[actual qty ]),ALLEXCEPT('Table','Table'[Line]))
Return
CALCULATE(DIVIDE(a,b),FILTER('Table','Table'[Week]<=MAXX(ALL('Table'),'Table'[Week])))
And you will see:
For the related .pbix file,pls click here.
Hi, Kelly, Thank you for your answer, you've provided one wonderful solution, but when I add more weeks of another year, it will be wrong, like this. Take Line12Q as example, it should be 18 (as raw data), but shows 11.47. (even I use weekNum, not week as calculation)
Workshop | 202014 |
W | 44.58 |
Line29 | 0.00 |
Line12Q | 11.47 |
Line12N | 4.18 |
Line30 | 40.92 |
Line131 | 0.00 |
Here is Raw Data
Workshop | Line No. | actual qty | Total | Week | Year | WeekNum |
W | Line12Q | 26208 | 15.8 | WK52 | 2019 | 201952 |
W | Line12Q | 26208 | 15.8 | WK51 | 2019 | 201951 |
W | Line12Q | 26208 | 15.8 | WK50 | 2019 | 201950 |
W | Line12Q | 26208 | 15.8 | WK49 | 2019 | 201949 |
W | Line12Q | 25725 | 18 | WK14 | 2020 | 202014 |
W | Line12Q | 25725 | 18 | WK13 | 2020 | 202013 |
W | Line12Q | 25725 | 18 | WK12 | 2020 | 202012 |
W | Line12Q | 25725 | 18 | WK11 | 2020 | 202011 |
W | Line12Q | 25725 | 18 | WK10 | 2020 | 202010 |
W | Line12Q | 25725 | 18 | WK09 | 2020 | 202009 |
W | Line12Q | 25725 | 18 | WK08 | 2020 | 202008 |
W | Line12Q | 25725 | 18 | WK03 | 2020 | 202003 |
W | Line12Q | 25725 | 18 | WK01 | 2020 | 202001 |
W | Line12Q | 94044 | 3.7 | WK52 | 2019 | 201952 |
W | Line12Q | 94044 | 3.7 | WK51 | 2019 | 201951 |
W | Line12Q | 94044 | 3.7 | WK50 | 2019 | 201950 |
W | Line12Q | 94044 | 3.7 | WK49 | 2019 | 201949 |
W | Line12Q | 7700 | 18 | WK14 | 2020 | 202014 |
W | Line12Q | 9000 | 18 | WK14 | 2020 | 202014 |
W | Line12Q | 7700 | 18 | WK13 | 2020 | 202013 |
W | Line12Q | 9000 | 18 | WK13 | 2020 | 202013 |
W | Line12Q | 7700 | 18 | WK12 | 2020 | 202012 |
W | Line12Q | 9000 | 18 | WK12 | 2020 | 202012 |
W | Line12Q | 7700 | 18 | WK11 | 2020 | 202011 |
W | Line12Q | 9000 | 18 | WK11 | 2020 | 202011 |
W | Line12Q | 7700 | 18 | WK10 | 2020 | 202010 |
W | Line12Q | 9000 | 18 | WK10 | 2020 | 202010 |
W | Line12Q | 7700 | 18 | WK09 | 2020 | 202009 |
W | Line12Q | 9000 | 18 | WK09 | 2020 | 202009 |
W | Line12Q | 7700 | 18 | WK08 | 2020 | 202008 |
W | Line12Q | 9000 | 18 | WK08 | 2020 | 202008 |
W | Line12Q | 7700 | 18 | WK03 | 2020 | 202003 |
W | Line12Q | 9000 | 18 | WK03 | 2020 | 202003 |
W | Line12Q | 7700 | 18 | WK01 | 2020 | 202001 |
W | Line12Q | 9000 | 18 | WK01 | 2020 | 202001 |
W | Line12Q | 12762 | 11 | WK52 | 2019 | 201952 |
W | Line12Q | 12762 | 11 | WK51 | 2019 | 201951 |
W | Line12Q | 12762 | 11 | WK50 | 2019 | 201950 |
W | Line12Q | 12762 | 11 | WK49 | 2019 | 201949 |
Try like
Col_NVL = CALCULATE(
divide(sumx('NVL','NVL'[actual qty]*'NVL'[Total]),sum(NVL[actual qty])),ALLEXCEPT('NVL',NVL[Line.]),FILTER(,'NVL'[Week]<=max('NVL'[Week])))
move all expect outside filter.
Thank you, amitchandak,
Yes, your answer is okay for Line summary, but when I go to upper level, workshop, it's wrong, as below:
Every line average is right, but when summarize to upper level, average (0,18,0,32,0), it sould be 10.16, but it shows 5.22. so error. data.
Workshop | 202014 |
W | 5.22 |
Line29 | 0.00 |
Line12Q | 18.00 |
Line12N | 0.00 |
Line30 | 32.80 |
Line131 | 0.00 |
I am trying to load raw data, but failed, because of too many characters.
So Here just some of whol raw data, for your information.
Workshop | Line No. | actual qty | Total | Week | Year | WeekNum |
W | Line 131 | 128721 | 0 | WK52 | 2019 | 201952 |
W | Line 131 | 128721 | 0 | WK51 | 2019 | 201951 |
W | Line 131 | 128721 | 0 | WK50 | 2019 | 201950 |
W | Line 131 | 128721 | 0 | WK49 | 2019 | 201949 |
W | Line12N | 12000 | 0 | WK14 | 2020 | 202014 |
W | Line 131 | 60000 | 0 | WK13 | 2020 | 202013 |
W | Line12N | 12000 | 0 | WK13 | 2020 | 202013 |
@Anonymous
Try something like this
averagex(summarize(Table,Table[Line No],Table[Workshop], "_avg", average(Table[ actual qty])),[_avg])
No, it's wrong, what I need is average of " sum(build QTY*total)/sum(Build QTY), I tried to add "*", but failed in average().
Hi @Anonymous not sure your MAX(week)...for cumulative? So I did not add this filter, you may modify it
@Vera_33 result shows, all lines output are the same, actually not. would you pls have a look the data which I sent to @v-kelly-msft , I don't want to accumulate it, but if I remove <=max(), then all line data are the same
Workshop Line. Week 02bbbb
W Line 131 WK14 230
W Line 30 WK14 230
W Line12N WK14 230
W Line12Q WK14 230
W Line29 WK14 230
@Anonymous ,
Try like
averagex(summarize(Table,Table[Line No],Table[Workshop],"_1",sumx(Table,Tablew[build QTY]*Table[total]), "_avg", sum(Table[Build QTY])),divide([_1],[_2]))
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |