Formula To Excel

[Yrstruly2021]

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.

[Anonymous]

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.

[govindarajan_d]

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]

It is not suppose to list values for every row.

[govindarajan_d]

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]

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]

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!

[govindarajan_d]

Hi @Yrstruly2021 ,

Did the provided solution work?