cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
Xavianna
Helper I
Helper I

Cumulative Sum & Percentage Calculations Without Using Rank Dax

Hello,

 

Following my previous post - https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Table-Multiple-Months-Sort-Issue/m-p...

 

Could I please have some advise on how to perform cumulative sum and percentages for each month without using the rank dax?

 

So now the data is displaying correctly by month order but the sum for May is incorrect. It should be 16 + 2 (June) = 18; then 18 + 3 (July) = 21. However, because July's figure is larger than June, it had ranked it 2 instead of 3. I can forsee this being an issue in the future when the dataset gets larger. Therefore, I would need a cumulative sum method that is not based on rank.

 

Additionally, in the percentage column, is it possible to have the cumulative % for each month and then the remainder % in the blank (month). At the moment, it is copying the same % as the last month. For example April's blank cell should have 100% - 66.10% = 33.9%.

 

Screenshot 2023-09-08 103757 Highlighted.png

 

Is this possible please? I hope it makes sense. Thank you so much for any help and advise in advance.

1 ACCEPTED SOLUTION

I have found the solution for this issue.

 

The final formula is

Total Filled YTD = TOTALYTD(SUM(Filled Bookings), DateTable[Date], "3-31")

View solution in original post

3 REPLIES 3
ChiragGarg2512
Impactful Individual
Impactful Individual

Try this DAX:

Cumm Based on Date = CALCULATE(Sum(Table[Booking filled]) , Window(1,ABS,0,REL, Summarize(ALLSELECTED('Table'), Table[Vacancy Month Year], Table[Date Filled Month Year], Table[Date Filled Month Year Sort]),ORDERBY('Table'[Date Filled Month Year Sort],ASC), PARTITIONBY(Table[Vacancy Month Year]) ))

 

For information refer to this video/blog:

Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f

For the formula below, I don't have a column for 'Table'[Date Filled Month Year Sort], what would this entail please?

 

Cumm Based on Date = CALCULATE(Sum(Table[Booking filled]) , Window(1,ABS,0,REL, Summarize(ALLSELECTED('Table'), Table[Vacancy Month Year], Table[Date Filled Month Year], Table[Date Filled Month Year Sort]),ORDERBY('Table'[Date Filled Month Year Sort],ASC), PARTITIONBY(Table[Vacancy Month Year]) ))

I also tried using Example 2 from here - https://learn.microsoft.com/en-us/dax/window-function-dax but it didn't work with the PARTITIONBY.

 

I got this working instead but it is not doing a cumulative as shown in the example, possible due to the PARTITIONBY issue.

 

Filled Rolling Sum = SUMX (WINDOW(1,ABS,0,REL,ALLSELECTED(Table[FilledYear],Table[FilledMonth]),ORDERBY(Table[FilledYear])),Table[Total Bookings Filled New Sum])
 
Filled Year and Filled Month are just extracted from the Date Filled column.
 
The example below is filtered for vacancies received in April. It may have slightly different  to the figures before because I have received new data for August.
 
Cumulative Issue (highlighted).png
 
The cumulative sum there is still based on the Ranking system which is not working well.
 
Additionally, I'm not sure why there are "Blank" month/year when the filled date column has a date. These 4 blank entries are for May 2023.
 
I have 2 date tables to display this. The 1st date table is for vacancy received and it has a relationship to the "Vacancy Received" date in the data table and the 2nd date table is for date filled and it has a relationship to the "Date Filled" date in the data table. Am I doing anything wrong to cause these blank entries please?
 
Thanks 🙂

I have found the solution for this issue.

 

The final formula is

Total Filled YTD = TOTALYTD(SUM(Filled Bookings), DateTable[Date], "3-31")

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors