Forum Discussion

Yrstruly2021's avatar
Yrstruly2021
Helper V
2 years ago

Formula To Excel

Pease give DAX for =-SUMIF(Details!B:B;'Cash Flow'!G34;Details!G:G)+4801. I have 

DebtorMovements =
CALCULATE(
    -SUM('ZTBR'[Amount in USD]),
    'ZTBR'[Roll_Up_Function] IN {"Debtor Movements"}
) + 4801
 
Is this correct?
It gives me the value, but that same value then displays for all the rows, which should not be.
DAX
Excel
formulas

7 Replies

  • govindarajan_d's avatar
    govindarajan_d
    Super User
    2 years ago

    Hi , 

    Can you try like this? 

    DebtorMovements =
CALCULATE (
    - SUM ( 'ZTBR'[Amount in USD] ),
    KEEPFILTERS ( 'ZTBR'[Roll_Up_Function] IN { "Debtor Movements" } )
) + 4801

    In your formula, it overwrites the existing filters (inc. filter context) and calculates the value. When you use KEEPFILTERS it respects the filter context. 

     

    For more info: https://www.sqlbi.com/articles/using-keepfilters-in-dax/

     

     

     

     

    Yrstruly2021

      • govindarajan_d's avatar
        govindarajan_d
        Super User
        2 years ago

        Try this:

         

        DebtorMovements=
IF (
    SELECTEDVALUE('ZTBR'[Roll_Up_Function])="Debtor Movements",
    CALCULATE (-SUM ('ZTBR'[Amount in USD]),'ZTBR'[Roll_Up_Function]="Debtor Movements")+4801,
    BLANK()
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Yrstruly2021 ,

     

    You can try formula like below:

    DebtorMovements = 
CALCULATE(
    -SUMX(
        FILTER(
            'ZTBR',
            'ZTBR'[Roll_Up_Function] = "Debtor Movements"
        ),
        'ZTBR'[Amount in USD]
    ),
    'ZTBR'[Roll_Up_Function] = "Debtor Movements"
) + 4801

     

    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.