Forum Discussion
Sum function with a filter is not working in a simple table
Sorry, forgot you already had added the index.
The reason it looks unsorted, is that when you add Date, Client and Code Project, your table gets sorted by Date, Client and Code Project, in that order. If you add index to your table visual, between Code project and Vente, you will see that the index is not displayed in strict ascending order. The column you created is based on strict ascending index. And unless you include the index in your table and sort by the index, the cumulative values will appear somewhat unsorted.
And you can't really solve this by using a measure either. If you don't want to add the index in your visual, you will have to do some work in Power Query/Edit queries. The reason for this is that in Power Query it is only possible to sort by a single column at a time, if you filter by a second column, it will forget the earlier sorting. So you need to get a bit creative with various indexes: https://community.powerbi.com/t5/Community-Blog/Using-EARLIER-with-a-SMART-INDEX/ba-p/809341.
If your data resides in a database, it will be easier to modify your queries.
Hi PatrickByGecko ,
I am not able to reproduce the behaviour you are experiencing. Please share your report, or if it contains data you cannot share, create a replicate report with made up data.
Cheers,
Sturla
Sturla
Of course I can send to you this report, but it get its data from sharepoint. Perhaps you will be able to use is only by the version saved of my report I mean with no intent of connection to sharepoint? Do you think it will be ok ? I'm not an expert.
No problem, I just won't be able to refresh the data, but I don't think that will be a problem
Here it is the pbix file => https://we.tl/t-yCr3x6bjJg
Alright!! 😀 Got it.
It's the fact that you are creating a cumulative value in a column that is playing tricks on you. When you add the cumulative-column to your table visualization, it automatically get summed. Because, you know, that is what Power BI is designed to do.
Take a look at your 'Activités Non Reglees'-table, and filter [Date regl] to 27.08.2019. There are 3 rows for this day, and the Cumul-columns is 1840 for all 3 of them. Now find the same date in your table visualization, and notice there are only 2 rows for this day. For the first of the rows the cumul column shows 1840, for the next one 3680(which is 1840+1840). So Cumul gets summed, because the other columns don't span sufficient distinct rows compared all the columns in 'Activités Non Reglees'.
So, I don't know your intentions with this cumul, but one way of avoiding the sum, is to change the default handling of the field to Don't summarize
Cheers,
Sturla
If this post helps, then please consider Accepting it as the solution. Kudos are nice too.