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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
Cooler
Frequent Visitor

Running total, self filtering by index column

Hello everyone.
I have a sales table and i drop  few columns from it into table visual,

product_group_id - product group
index - unique number of sale

the main column is a index - from 0, all the values of index are unique. In sales table i have around 300K rows.

Cooler_0-1729618819606.png

 


And i need to calculate in measure running total not a general but within the group, so i need for every sale in sales table calculate running total within product group, based on index, e.g. all sales of the same group before and including current sale by index.
here is my code

sales_runn_total8 =
            VAR cur_index = SELECTEDVALUE ( sales[Index] )
            VAR gr_id = MAX ( products[IDProductGroup] )
            VAR sale_table =
                CALCULATETABLE(
                    sales,
                        REMOVEFILTERS ( sales ),
                            products[IDProductGroup] = gr_id
                            )
        VAR run_total =
            SUMX(FILTER(sale_table, [Index] <= cur_index), [ammount])  
                       
        RETURN
 run_total
 
basically it works, but the measure is slow , it takes about 30-40 sec , and the reason of it is filtering nested sales table with current index in visual. So it works very fast if i calculate all sales of the current group, but the moment i am trying using any variable from base table, sales table, like index or any other, it became 10 times slower than before ((
please any ideas of workaround welcome!))
p.s. calculated column and PQ are not considering ))
2 REPLIES 2
lbendlin
Super User
Super User

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? 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.

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

Hello. i made a correction to my initial post and here is my PBIX file .
Example.pbix

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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