Forum Discussion

PatrickByGecko's avatar
PatrickByGecko
Helper V
6 years ago
Solved

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])))
 
Need Help
  • sturlaws's avatar
    sturlaws
    6 years ago

    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.

     

     

26 Replies

  • sturlaws's avatar
    sturlaws
    Resident Rockstar
    6 years ago

    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.

      • sturlaws's avatar
        sturlaws
        Resident Rockstar
        6 years ago

        sorry about that, wrap a calculate around the selectvalue-function:

        VAR _currentRowDate = CALCULATE(SELECTEDVALUE ( 'Table'[vDate] ))
  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support
    6 years ago

    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] )
    )
)

     

      • sturlaws's avatar
        sturlaws
        Resident Rockstar
        6 years ago

        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