topic Re: need help with a formule in DAX Commands and Tips

need help with a formule

Krijgersss — Wed, 04 Jun 2025 10:46:33 GMT

I use this formula in Power BI in a matrix to show only values greater than or equal to 0 at the total level (top layer). I do not want to see negative values at the total level, but negative values should still be visible in the underlying (detail) level. So the total must always be >= 0.

I am currently using this formula, but there are still 2 values showing as negative at the total level. Does anyone have a solution to make this work correctly?

Bedrag (Positieve klanten, met totaal) =

VAR KlantTotaal =

CALCULATE(

SUM(Cashflow_Paymentterms[amountdc]),

ALLEXCEPT(Cashflow_Paymentterms, Accounts[Relatie])

)

VAR BedragZichtbaarKlant =

IF(KlantTotaal >= 0, SUM(Cashflow_Paymentterms[amountdc]))

VAR IsTotaalNiveau =

NOT ISINSCOPE(Accounts[Relatie])

RETURN

IF(

IsTotaalNiveau,

CALCULATE(

SUMX(

VALUES(Accounts[Relatie]),

VAR TotaalPerKlant =

CALCULATE(SUM(Cashflow_Paymentterms[amountdc]))

RETURN IF(TotaalPerKlant >= 0, TotaalPerKlant)

)

BedragZichtbaarKlant

)

Re: need help with a formule

Rupak_bi — Wed, 04 Jun 2025 10:54:46 GMT

Hi Krijgersss

Try This

Bedrag (Positieve klanten, met totaal) =

VAR KlantTotaal =

CALCULATE(

SUM(Cashflow_Paymentterms[amountdc]),

ALLEXCEPT(Cashflow_Paymentterms, Accounts[Relatie])

)

VAR BedragZichtbaarKlant = max(0, KlantTotaal)
return

if(ISINSCOPE(Accounts[Relatie]), KlantTotaal, BedragZichtbaarKlant)

Re: need help with a formule

Krijgersss — Wed, 04 Jun 2025 10:58:29 GMT

That unfortunately doesn't work.

Re: need help with a formule

techies — Wed, 04 Jun 2025 11:16:47 GMT

Hi Krijgersss please try this

measure =

VAR IsTotalLevel = NOT HASONEVALUE(Accounts[Relatie])

RETURN

IF (

IsTotalLevel,

SUMX (

FILTER (

VALUES (Accounts[Relatie]),

CALCULATE(SUM(Cashflow_Paymentterms[amountdc])) >= 0

CALCULATE(SUM(Cashflow_Paymentterms[amountdc]))

SUM(Cashflow_Paymentterms[amountdc])

)

Re: need help with a formule

Rupak_bi — Wed, 04 Jun 2025 11:21:25 GMT

plz share the image of the out put you are getting. so that it can be corrected.

Re: need help with a formule

Krijgersss — Wed, 04 Jun 2025 11:37:08 GMT

with this solutions it only shows me more negative values as Total i dont want any negative as a total all the total values of the up layer has to be >=0 The underlying level may have negative values since these are summed up to the total.

Re: need help with a formule

techies — Wed, 04 Jun 2025 17:35:53 GMT

Hi Krijgersss pls share sample pbix file if possible

Re: need help with a formule

v-achippa — Mon, 09 Jun 2025 12:24:23 GMT

Hi Krijgersss.,

Thank you for reaching out to Microsoft Fabric Community.

Thank you techies and Rupak_bi for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user resolved your issue? or let us know if you need any further assistance.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data?

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

If any response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

Re: need help with a formule

v-achippa — Fri, 13 Jun 2025 05:06:09 GMT

Hi @Krijgersss.,

As we haven’t heard back from you, we wanted to kindly follow up to check if your issue is resolved or if any response helped in resolving the issue.

If any response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

Re: need help with a formule

FBergamaschi — Fri, 13 Jun 2025 13:14:19 GMT

Pls share a file where we can look at the issue

Re: need help with a formule

v-achippa — Mon, 16 Jun 2025 13:02:13 GMT

Hi @Krijgersss.,

We wanted to kindly follow up to check if your issue is resolved or if any response helped in resolving the issue.

If any response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa