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...
DAX
Excel
formulas
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

Yrstruly2021's avatar
Yrstruly2021
Helper V
2 years ago

It is not suppose to list values for every row.

 

  • 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()
)
    • Yrstruly2021's avatar
      Yrstruly2021
      Helper V
      2 years ago

      This code eliminate the assigned values to rows that shoul not have these values, but it does not give me the total like the other codes does.

       

      • govindarajan_d's avatar
        govindarajan_d
        Super User
        2 years ago

        Hi Yrstruly2021 ,

         

        Please try this:

        DebtorMovements=
IF(
	HASONEVALUE('ZTBR'[Roll_Up_Function]),
	IF (
		SELECTEDVALUE('ZTBR'[Roll_Up_Function])="Debtor Movements",
		CALCULATE (-SUM ('ZTBR'[Amount in USD]),'ZTBR'[Roll_Up_Function]="Debtor Movements")+4801,
		BLANK()
	),
	CALCULATE (-SUM ('ZTBR'[Amount in USD]),'ZTBR'[Roll_Up_Function]="Debtor Movements")+4801
)

         

        Upvote and accept as solution if this helped!