Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi All,
I have my data as below.
ID | OUTPUT_TYPE | FORECASTED_MONTH | FORECAST_QTY | Cumm. FORECAST_QTY |
1 | NOMINAL | 3 | 26 | 26 |
1 | NOMINAL | 6 | 27 | 54 |
1 | NOMINAL | 9 | 28 | 82 |
1 | NOMINAL | 12 | 29 | 111 |
1 | NOMINAL | 18 | 60 | 172 |
1 | NOMINAL | 24 | 62 | 234 |
1 | NOMINAL | 36 | 127 | 360 |
1 | NOMINAL | 48 | 128 | 488 |
1 | NOMINAL | 60 | 127 | 615 |
1 | UPPER_BOUND | 3 | 32 | 26 |
1 | UPPER_BOUND | 6 | 34 | 66 |
1 | UPPER_BOUND | 9 | 36 | 102 |
1 | UPPER_BOUND | 12 | 37 | 139 |
1 | UPPER_BOUND | 18 | 78 | 217 |
1 | UPPER_BOUND | 24 | 82 | 299 |
1 | UPPER_BOUND | 36 | 170 | 469 |
1 | UPPER_BOUND | 48 | 172 | 641 |
1 | UPPER_BOUND | 60 | 169 | 810 |
1 | LOWER_BOUND | 3 | 22 | 26 |
1 | LOWER_BOUND | 6 | 22 | 44 |
1 | LOWER_BOUND | 9 | 23 | 67 |
1 | LOWER_BOUND | 12 | 23 | 90 |
1 | LOWER_BOUND | 18 | 46 | 136 |
1 | LOWER_BOUND | 24 | 46 | 182 |
1 | LOWER_BOUND | 36 | 92 | 274 |
1 | LOWER_BOUND | 48 | 91 | 365 |
1 | LOWER_BOUND | 60 | 89 | 454 |
Where last column Comm. ForeCast_Qty is Cumulative sum of Forecast Qty. I want to generate Comm. Forecast_Qty using DAX expression. But it is not coming as expected.
I have used below formula
ID | FORECAST_MONTH | OUTPUT_TYPE | FORECAST_QTY | Cumm. FORECAST_QTY |
1 | 3 | UPPER_BOUND | 32 | 1932.113659 |
1 | 6 | UPPER_BOUND | 34 | 3987.743786 |
1 | 9 | UPPER_BOUND | 36 | 6153.976373 |
1 | 12 | UPPER_BOUND | 37 | 8417.15069 |
1 | 18 | UPPER_BOUND | 78 | 13180.17074 |
1 | 24 | UPPER_BOUND | 82 | 18176.57163 |
1 | 36 | UPPER_BOUND | 170 | 28551.03983 |
1 | 48 | UPPER_BOUND | 172 | 39048.55704 |
1 | 60 | UPPER_BOUND | 169 | 49351.2418 |
1 | 3 | NOMINAL | 26 | 1603.994892 |
1 | 6 | NOMINAL | 27 | 3279.197087 |
1 | 9 | NOMINAL | 28 | 5012.524375 |
1 | 12 | NOMINAL | 29 | 6793.830674 |
1 | 18 | NOMINAL | 60 | 10468.45101 |
1 | 24 | NOMINAL | 62 | 14250.6209 |
1 | 36 | NOMINAL | 127 | 21989.71836 |
1 | 48 | NOMINAL | 128 | 29789.28012 |
1 | 60 | NOMINAL | 127 | 37507.12435 |
1 | 3 | LOWER_BOUND | 22 | 1328.185432 |
1 | 6 | LOWER_BOUND | 22 | 2688.436389 |
1 | 9 | LOWER_BOUND | 23 | 4067.609045 |
1 | 12 | LOWER_BOUND | 23 | 5458.004435 |
1 | 18 | LOWER_BOUND | 46 | 8256.060605 |
1 | 24 | LOWER_BOUND | 46 | 11062.8622 |
1 | 36 | LOWER_BOUND | 92 | 16664.66502 |
1 | 48 | LOWER_BOUND | 91 | 22206.62438 |
1 | 60 | LOWER_BOUND | 89 | 27649.1102 |
I am not understanding where is the mistake. Please help me to solve this.
Thanks,
Rajesh S Hegde
Solved! Go to Solution.
@Anonymous , Try like
Cummulative Qty New =
CALCULATE(
SUM('Forecasting'[FORECAST_QTY]),
FILTER(
allselected('Forecasting'),'Forecasting'[ID],'Forecasting'[OUTPUT_TYPE] max('Forecasting'[OUTPUT_TYPE]) &&
'Forecasting'[FORECAST_MONTH] <= MAX('Forecasting'[FORECAST_MONTH])
)
)
Hello good afternoon I am in a dilemma here the %TO Voluntary I get 9.05% the total but when I want to show in graph I get a lower value 8.1. what I must do so that when I make a graph I get the correct total % .
I put the image of the graph that does not match the total quantities.
Please support.
Hi @Anonymous ,
Here are the steps you can follow:
1. Create calculcated column.
Cumm. FORECAST_QTY =
CALCULATE(SUM('Table'[FORECAST_QTY]),FILTER('Table','Table'[Index]<=EARLIER('Table'[Index])&&'Table'[OUTPUT_TYPE]=EARLIER('Table'[OUTPUT_TYPE])))
2. Result.
You can downloaded PBIX file from here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you @ryan_mayu , it is working as expected. I had additional columns and hence it was not coming. Now I have removed and it is working fine
@Anonymous , Try like
Cummulative Qty New =
CALCULATE(
SUM('Forecasting'[FORECAST_QTY]),
FILTER(
allselected('Forecasting'),'Forecasting'[ID],'Forecasting'[OUTPUT_TYPE] max('Forecasting'[OUTPUT_TYPE]) &&
'Forecasting'[FORECAST_MONTH] <= MAX('Forecasting'[FORECAST_MONTH])
)
)
Thank you @amitchandak, it is working as expected. I had additional columns and hence it was not coming. Now I have removed and it is working fine
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
117 | |
109 | |
109 | |
93 | |
69 |
User | Count |
---|---|
173 | |
135 | |
131 | |
96 | |
94 |