topic Re: if subtracting value in aggregation in DAX Commands and Tips

if subtracting value in aggregation

Anonymous — Tue, 19 Jul 2022 13:40:50 GMT

Hello, when I'm doing the conditional itself, checking the number and less than zero, the result is true and 0, if for false then and the original value.

Exemple:

if [number] <0, 0, [number]

as it should return:

pay attention to the table total (aggregation)

product	value	if column
a	10	10
b	-5	0
c	-7	0
d	1	1
e	3	3
f	1	1
total	3	15

-------------------------------------------
how you're returning:

produto	valor	se coluna
um	10	10
b	-5	0
c	-7	0
d	1	1
e	3	3
f	1	1
total	3	3

How do I fix it?

Re: if subtracting value in aggregation

amitchandak — Tue, 19 Jul 2022 14:21:35 GMT

Anonymous , if [number] is a measure then try like

sumx(values(Table[Producto) , if([number] <0, 0, [number]) )

Re: if subtracting value in aggregation

Anonymous — Tue, 19 Jul 2022 16:28:17 GMT

Thank you very much, worked as advised, could you please tell me the logic that built for this solution ?

Re: if subtracting value in aggregation

amitchandak — Tue, 19 Jul 2022 16:30:49 GMT

Anonymous , As the grand total is calculated again, it does not have a row context. We use Values or summarize to push the group by visual so that grand total considers that and calculates each row by row and adds up

Re: if subtracting value in aggregation

Anonymous — Tue, 19 Jul 2022 16:33:25 GMT

fantastic, thank you again, I will subscribe to your channel to follow and thank you for the help !