Forum Discussion

Krijgersss's avatar
Krijgersss
Helper II
1 year ago
Solved

need help with a formule

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's avatar
    v-achippa
    1 year ago

    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

10 Replies

  • Rupak_bi's avatar
    Rupak_bi
    Super User
    1 year ago
     
    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)
        
  • techies's avatar
    techies
    Super User
    1 year ago

    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's avatar
      Krijgersss
      Helper II
      1 year ago

      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.

  • Rupak_bi's avatar
    Rupak_bi
    Super User
    1 year ago

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

  • v-achippa's avatar
    v-achippa
    Community Support
    1 year ago

    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's avatar
      v-achippa
      Community Support
      1 year ago

      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's avatar
        v-achippa
        Community Support
        1 year ago

        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