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 anoth...
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.

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.