need help with a formule

[Krijgersss]

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

    )

[v-achippa · Accepted solution]

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

[v-achippa]

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

[v-achippa]

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

[Rupak_bi]

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

[techies]

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

    )

[Krijgersss]

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.

[techies]

Hi @Krijgersss pls share sample pbix file if possible

[Rupak_bi]

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)

[Krijgersss]

That unfortunately doesn't work.

[FBergamaschi]

Pls share a file where we can look at the issue