Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'd like to create calculated columns to get cumulative sum of sets ordered and sets shipped to have fill rate cumulative data, and if I select more than 2 weeks the calculation considers the data for those dates as the whole range
can somebody help?
Date | Order | Fill Rate | Status | Sets Ordered | Sets shipped | fill rate | cumulated sets ordered | cumulated sets shipped | fill rate **bleep** |
19-Apr-21 | O1001517088 | 96.43% | Shipped>=95% | 10945 | 10554 | 0.964276 | 10945 | 10554 | 0.964275925 |
19-Apr-21 | O1001517075 | 95.10% | Shipped>=95% | 9477 | 9013 | 0.951039 | 20422 | 19567 | 0.958133386 |
19-Apr-21 | O1001515196 | 95.87% | Shipped>=95% | 8400 | 8053 | 0.95869 | 28822 | 27620 | 0.958295746 |
26-Apr-21 | O1001526467 | 88.34% | Shipped<95% | 9612 | 8491 | 0.883375 | 38434 | 36111 | 0.939558724 |
26-Apr-21 | O1001524641 | 89.43% | Shipped<95% | 10220 | 9140 | 0.894325 | 48654 | 45251 | 0.930057138 |
26-Apr-21 | O1001524629 | 96.52% | Shipped>=95% | 9630 | 9295 | 0.965213 | 58284 | 54546 | 0.935865761 |
Solved! Go to Solution.
Hi @Anonymous ,
Try:
Measure =
var ordered_ = CALCULATE(SUM('Table'[Sets Ordered]),ALLSELECTED('Table'))
var shipped_ = CALCULATE(SUM('Table'[Sets shipped]),ALLSELECTED('Table'))
return DIVIDE(shipped_,ordered_,0)
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
After adding an index column in the query editor, create the following column:
Column = CALCULATE(SUM('Table'[Sets Ordered]),FILTER('Table',EARLIER('Table'[Date])>='Table'[Date]&&'Table'[Index]<=EARLIER('Table'[Index])))
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
thanks for the feedback!
what I am missing now is to have a final fill rate by week, so if I add two or more weeks the fill rate changes accordingly
Hi @Anonymous ,
Try:
Measure =
var ordered_ = CALCULATE(SUM('Table'[Sets Ordered]),ALLSELECTED('Table'))
var shipped_ = CALCULATE(SUM('Table'[Sets shipped]),ALLSELECTED('Table'))
return DIVIDE(shipped_,ordered_,0)
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
thanks a lot! It works perfectly 😎
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |