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.
I did the same as you.
And as you can see there is again a problem on line 27/9/2019 / EASYGREEN.
That's because you are using [Index] in you dax, as someone else proposed. Then you are sort looking at row numbers. Rewrite your code to this:
Cumul =
var _currentDate=CALCULATE(SELECTEDVALUE('Activités Non Reglees'[DATE REGL]))
return
CALCULATE (
SUM ( 'Activités Non Reglees'[VENTE] );
FILTER (
'Activités Non Reglees';
'Activités Non Reglees'[DATE REGL] <= _currentDate
)
)
and it will work like a charm
Cheers,
Sturla
If this post helps, then please consider Accepting it as the solution. Kudos are nice too.
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.
I'am sorry but even with this new dax formule I always get some troubles (have a look form 12/11/2019).
Hello Sturla
Did you find any solution?
Patrick
The formula is only considering date, so for 12/11/2019, the value should be the same, as in your screenshot. If this is not your desired behaviour, you will need to use at least one other column to filter in the filter-expression. Perhaps [Code Project]. Or if you can trust the read in-order of your data, create an index in Power Query, and use this as part of the filter.
create a new variable:
var _currentIndex = CALCULATE ( SELECTEDVALUE ( 'table'[Index] ) )and add this to your filter statement
&& 'Table'[Index] <= _currentIndexIt is what I have done (i'm going crazy..)
Here the report => https://we.tl/t-I5YilIxYc9
And here is underneath the result (even when indexed the sum is not progressive by 12/10/2019).
Hello
The main point was that it is necessary to check that vizualization in power bi for allcolumns is exactly in the same order than the index....
Thanks to all of you for this help.