Problem with Totals and Minimum

[santigc97]

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)

[v-xuxinyi-msft]

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]

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

Weeks	Position	Location Risks	Atributo	Valor
W 0-3	Manager	A	7. SALARY	-1165
W 0-3	Manager	B	7. SALARY	-21
W 0-3	Manager	C	7. SALARY	-4
W 0-3	Manager	D	7. SALARY	0
W 3-5	Manager	A	7. SALARY	1000
W 3-5	Manager	B	7. SALARY	0
W 3-5	Manager	C	7. SALARY	0
W 3-5	Manager	D	7. SALARY	-5

[v-xuxinyi-msft · Accepted solution]

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.