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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply

Sum function with a filter is not working in a simple table

Hello everybody,

As you can see underneath my column "Cumul" (Cumulate) does not receive a progressive Sum of my "Vente" (Sales). The sum is erratic in the column "Cumul". I don't  understand it looks very simple yet..

 

Cumul = calculate(SUM ('Activités Non Reglees'[VENTE]);FILTER('Activités Non Reglees';'Activités Non Reglees'[DATE REGL].[Date]<=EARLIER('Activités Non Reglees'[DATE REGL].[Date])))
ScreenShotPowerbi.jpg
 
1 ACCEPTED SOLUTION

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.

 

 

View solution in original post

26 REPLIES 26
v-eachen-msft
Community Support
Community Support

Hi @PatrickByGecko ,

 

You could add an index column in the query editor( for example, 'Activités Non Reglees'[Index] ). Then use the following DAX:

Cumul =
CALCULATE (
    SUM ( 'Activités Non Reglees'[VENTE] );
    FILTER (
        'Activités Non Reglees';
        'Activités Non Reglees'[Index] <= EARLIER ( 'Activités Non Reglees'[Index] )
    )
)

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

I managed to add an index but it did not change anything.

ScreenShotPowerbi.jpg

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

Sum function with a filter is not working in a simple table.PNG

 

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

I did as you told (do not summarize) but when the dates are sorted on left, the sum is always wrong on right as you can see underneath.

I send you again my report. => https://we.tl/t-IghgrmsbuX

 

ScreenShotPowerbi.gif

If you look in the fields of you visual, you can see that it is still summed:
Sum function with a filter is not working in a simple table 3.PNG

 

If you click on the arrow there, choose "Don't summarize" here as well. This will give you this picture:
Sum function with a filter is not working in a simple table 2.PNG

 

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.

I'am sorry but even with this new dax formule I always get some troubles (have a look form 12/11/2019).ScreenShotPowerbi.gif

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] <= _currentIndex

It 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).

Sans titre-1.jpg

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.

 

 

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.

Hello Sturla

Did you find any solution?

Patrick

sturlaws
Resident Rockstar
Resident Rockstar

Hi, @PatrickByGecko 

 

try this measure

Cumul =
VAR _currentRowDate =
    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] <= _currentRowDate
        )
    )

 

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

I get no error but now the table becomes empty.

ScreenShotPowerbi.jpg

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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