topic Re: Formula To Excel in DAX Commands and Tips

Formula To Excel

Yrstruly2021 — Tue, 06 Feb 2024 12:19:13 GMT

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.

Re: Formula To Excel

govindarajan_d — Tue, 06 Feb 2024 12:32:02 GMT

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

Re: Formula To Excel

Yrstruly2021 — Tue, 06 Feb 2024 12:40:33 GMT

It is not suppose to list values for every row.

Re: Formula To Excel

govindarajan_d — Tue, 06 Feb 2024 13:15:27 GMT

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

Re: Formula To Excel

Anonymous — Wed, 07 Feb 2024 07:08:53 GMT

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.

Re: Formula To Excel

Yrstruly2021 — Wed, 07 Feb 2024 13:46:14 GMT

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.

Re: Formula To Excel

govindarajan_d — Wed, 07 Feb 2024 14:36:11 GMT

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!

Re: Formula To Excel

govindarajan_d — Fri, 09 Feb 2024 07:44:14 GMT

Hi Yrstruly2021 ,

Did the provided solution work?