Forum Discussion

santigc97's avatar
santigc97
Frequent Visitor
2 years ago
Solved

Problem with Totals and Minimum

Hello everyone,

 

I have a problem with a table, I have a measure and I want a sum on the total, is there an option to modify the totals and so a sum like a pivot table on Excel? I have tried another option and it is to create a modified measure but I don't achieve the correct result.

Note that in the table (I exported it) I have the sum but for all the classes without filtering in the total (it is a test) and in the one that is without the hasonevalue I have the minimum.

 

SalarioMinimo =
VAR MinimoValor =
MINX(
FILTER(
TEST,
TEST[Atributo] = "7. SALARY" && TEST[Valor] < 0 && TEST[Weeks] = "W 0-3"
),
TEST[Valor]
)
RETURN
IF(ISBLANK(MinimoValor), BLANK(), MinimoValor)

 

 

SalarioMinimoTotal =
VAR MinimoValor =
MINX(
FILTER(
TEST,
TEST[Atributo] = "7. SALARY" && TEST[Valor] < 0 && TEST[Weeks] = "W 0-3"
),
TEST[Valor]
)
RETURN
IF(HASONEVALUE(TEST[Location Risks]),
IF(ISBLANK(MinimoValor), BLANK(), MinimoValor),
SUMX(VALUES(TEST[Location Risks]),MinimoValor))
 
The total that i want is -1190 (the sum of all of the negatives)

 


 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi santigc97 

     

    I would like to apologize for the belated reply.

     

    After my test, you can use the following measure:

    SalarioMinimo = 
VAR MinimoValor =
SUMX(
FILTER(
TEST,
TEST[Atributo] = "7. SALARY" && TEST[Valor] < 0 && TEST[Weeks] = "W 0-3"
),
TEST[Valor]
)
RETURN
IF(ISBLANK(MinimoValor), BLANK(), MinimoValor)

     

    Result:

     

    Best Regards,
    Yulia Xu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi santigc97 

     

    I have understood your requirement but I am not able to reproduce your formulas without sample data, can you provide PBIX file or sample data in excel or screenshot form? This will make it easier for us to find the solution. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

     

    Best Regards,
    Yulia Xu

    • santigc97's avatar
      santigc97
      Frequent Visitor
      2 years ago

      Hello!

       

      Thanks for your repply

       

      Find part of my dataset (you need to introduce it as table and call it TEST, also it is in a bad format you can copy paste it).

       

      Note that i want the sum of mins (if the value is positive i prefer to not appear) and with the sum of the totals (-1165-21-4)=-1190 instead of the minimal value that is -1165

       

      Measure 1

       

      SalarioMinimo =
      VAR MinimoValor =
      MINX(
      FILTER(
      TEST,
      TEST[Atributo] = "7. SALARY" && TEST[Valor] < 0 && TEST[Weeks] = "W 0-3"
      ),
      TEST[Valor]
      )
      RETURN
      IF(ISBLANK(MinimoValor), BLANK(), MinimoValor)
       
      Measure 2
       
      SalarioMinimoTotal =
      VAR MinimoValor =
      MINX(
      FILTER(
      TEST,
      TEST[Atributo] = "7. SALARY" && TEST[Valor] < 0 && TEST[Weeks] = "W 0-3"
      ),
      TEST[Valor]
      )
      RETURN
      IF(HASONEVALUE(TEST[Location Risks]),
      IF(ISBLANK(MinimoValor), BLANK(), MinimoValor),
      SUMX(VALUES(TEST[Location Risks]),MinimoValor))

       

       

       

      WeeksPositionLocation RisksAtributoValor
      W 0-3ManagerA7. SALARY-1165
      W 0-3ManagerB7. SALARY-21
      W 0-3ManagerC7. SALARY-4
      W 0-3ManagerD7. SALARY0
      W 3-5ManagerA7. SALARY1000
      W 3-5ManagerB7. SALARY0
      W 3-5ManagerC7. SALARY0
      W 3-5ManagerD7. SALARY-5
      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi santigc97 

         

        I would like to apologize for the belated reply.

         

        After my test, you can use the following measure:

        SalarioMinimo = 
VAR MinimoValor =
SUMX(
FILTER(
TEST,
TEST[Atributo] = "7. SALARY" && TEST[Valor] < 0 && TEST[Weeks] = "W 0-3"
),
TEST[Valor]
)
RETURN
IF(ISBLANK(MinimoValor), BLANK(), MinimoValor)

         

        Result:

         

        Best Regards,
        Yulia Xu

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.