Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Anonymous
Not applicable

Cumulative Sum using DAX

Hi All,

 

I have my data as below.

 

IDOUTPUT_TYPEFORECASTED_MONTHFORECAST_QTYCumm. FORECAST_QTY
1NOMINAL32626
1NOMINAL62754
1NOMINAL92882
1NOMINAL1229111
1NOMINAL1860172
1NOMINAL2462234
1NOMINAL36127360
1NOMINAL48128488
1NOMINAL60127615
1UPPER_BOUND33226
1UPPER_BOUND63466
1UPPER_BOUND936102
1UPPER_BOUND1237139
1UPPER_BOUND1878217
1UPPER_BOUND2482299
1UPPER_BOUND36170469
1UPPER_BOUND48172641
1UPPER_BOUND60169810
1LOWER_BOUND32226
1LOWER_BOUND62244
1LOWER_BOUND92367
1LOWER_BOUND122390
1LOWER_BOUND1846136
1LOWER_BOUND2446182
1LOWER_BOUND3692274
1LOWER_BOUND4891365
1LOWER_BOUND6089454

 

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

 

Cummulative Qty New =
CALCULATE(
SUM('Forecasting'[FORECAST_QTY]),
FILTER(
ALLEXCEPT('Forecasting','Forecasting'[ID],'Forecasting'[OUTPUT_TYPE]),
MAX('Forecasting'[FORECAST_MONTH]) >= 'Forecasting'[FORECAST_MONTH]
)
)
 
But the output is not coming as expected and I am getting below output.
 
IDFORECAST_MONTHOUTPUT_TYPEFORECAST_QTYCumm. FORECAST_QTY
13UPPER_BOUND321932.113659
16UPPER_BOUND343987.743786
19UPPER_BOUND366153.976373
112UPPER_BOUND378417.15069
118UPPER_BOUND7813180.17074
124UPPER_BOUND8218176.57163
136UPPER_BOUND17028551.03983
148UPPER_BOUND17239048.55704
160UPPER_BOUND16949351.2418
13NOMINAL261603.994892
16NOMINAL273279.197087
19NOMINAL285012.524375
112NOMINAL296793.830674
118NOMINAL6010468.45101
124NOMINAL6214250.6209
136NOMINAL12721989.71836
148NOMINAL12829789.28012
160NOMINAL12737507.12435
13LOWER_BOUND221328.185432
16LOWER_BOUND222688.436389
19LOWER_BOUND234067.609045
112LOWER_BOUND235458.004435
118LOWER_BOUND468256.060605
124LOWER_BOUND4611062.8622
136LOWER_BOUND9216664.66502
148LOWER_BOUND9122206.62438
160LOWER_BOUND8927649.1102

 

I am not understanding where is the mistake. Please help me to solve this.

 

Thanks,

Rajesh S Hegde

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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])
)
)

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

7 REPLIES 7
Syndicate_Admin
Administrator
Administrator

Carlos_Pando_0-1663093602734.png

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.

Carlos_Pando_1-1663093699470.png

Please support.

v-yangliu-msft
Community Support
Community 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.

v-yangliu-msft_0-1605834789471.jpeg

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.

ryan_mayu
Super User
Super User

@Anonymous 

you DAX should work fine.please see the attachment.

1.PNG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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

amitchandak
Super User
Super User

@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])
)
)

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

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

Anonymous
Not applicable

Hi @amitchandak , 
 
I have tried like below. But it is not working as expected.
 
Cummulative Qty New = CALCULATE(
SUM('Forecasting'[FORECAST_QTY]),
FILTER(
ALLSELECTED('Forecasting'),'Forecasting'[ID] = SELECTEDVALUE('Forecasting'[ID]) &&
'Forecasting'[OUTPUT_TYPE] = max('Forecasting'[OUTPUT_TYPE]) &&
'Forecasting'[FORECAST_MONTH] <= MAX('Forecasting'[FORECAST_MONTH])
)
)
 
 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.