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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Clanmaster
New Member

Create dynamic index to calculate cumulative sum

Wanted to create dyanmic index so whenver any slicer or any column filed sort the index will be not skip/flitered the value, it should reset every time and start value from 1 an so on. 

 

I have tried using rankx but its working fine without slicer when we select slicer value index will be filter and reslut get impacted.

index_ = RANKX(ALL(SQLMain),SQLMain[SKU],,ASC)
 
 running/cumulative sum -
r_sum = SUMX(FILTER(ALL(SQLMain),SQLMain[index_]<=EARLIER(SQLMain[index_])),SQLMain[**bleep**])
 

No selection slicer -

Clanmaster_0-1689940191492.png

 


 

Selection on slicer -

Clanmaster_1-1689940303942.png

 

My aim to calculate cumulative sum. once Index will be correct it other cumulative calculation work fine. 

 

note -in my data set i dont have any filed like date so for refrenece of privous value to continue to calulate the sum. 

 

2 REPLIES 2
Clanmaster
New Member

Hi Floks,

Sharing Test PBIX and Two excel file

1. Test_without_slicer_selection (correct calculation of running sum) 2. Test_with_slicer_selection (once slicer apply running sum not calculated as per pervious data its took data behind it which is filter so We can say that its not taking pervious value dynamically when slicer applied).

 

Fields details - SwapsLast3weeks_0 - nested field which created in db using SQL query MAXswap - nested field which created in db using SQL query. Index- As per my logic , I have created Index because of to calculated running sum/cumulative sum it took previous value to add in current value.

 

I want to calculate running sum/cumulative sum- its working fine when no selection in slicer once we select any value in slicer index not be recreate as current filed can't took previous value to calculate running sum.

 

Attached files here - https://drive.google.com/drive/folders/1YENWaojKTNUmdTtv8DIgWGcX98Eg-gyn

foodd
Super User
Super User

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).


https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...


Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

 

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors