topic Re: Question about DAX Formula with positiv and negativ counts in DAX Commands and Tips

Question about DAX Formula with positiv and negativ counts

Natascha_D — Mon, 16 Sep 2024 09:29:44 GMT

hello,

i have a problem with my dax formula.

In my matrix, I have a column with negative and positive values (actual numbers) and a column with target values (sums). My dax formula calculates the negative values (-values) correctly, but it always adds the positive values, because minus and minus gives plus, the formulas had to be like this for the negative values.

For the positive values, however, I can't manage to convert the formula correctly so that it subtracts the actual target values: Solution_time_plus_target =
IF(
SUMX(‘complete report FMIT Helpdesk’, ‘complete report FMIT Helpdesk’[FM solution time TARGET]) >= 0,
SUM(‘complete report FMIT Helpdesk’[resolution time final]) + SUMX(‘complete report FMIT Helpdesk’, ‘complete report FMIT Helpdesk’[FM resolution time TARGET]),
SUM(‘complete report FMIT Helpdesk’[FM resolution time TARGET]) -
SUMX(
‘complete report FMIT Helpdesk’,
IF(
‘complete report FMIT Helpdesk’[resolution time final] < 0,
‘complete report FMIT Helpdesk’[resolution time final],
0
)
)
)

Maybe one of you knows the correct answert. Thank you for helping. Natascha

Re: Question about DAX Formula with positiv and negativ counts

bhanu_gautam — Mon, 16 Sep 2024 09:55:03 GMT

Natascha_D , Try using below measure

Solution_time_plus_target =
SUMX(
'complete report FMIT Helpdesk',
IF(
'complete report FMIT Helpdesk'[FM solution time TARGET] >= 0,
'complete report FMIT Helpdesk'[resolution time final] - 'complete report FMIT Helpdesk'[FM solution time TARGET],
'complete report FMIT Helpdesk'[resolution time final] + 'complete report FMIT Helpdesk'[FM solution time TARGET]
)
)

Re: Question about DAX Formula with positiv and negativ counts

Natascha_D — Mon, 16 Sep 2024 12:35:49 GMT

Hi bhanu_gautam

Thank you very much for your answer. I tried but the positive values get allready wrong. The system make the addition not the subtraktion....

Re: Question about DAX Formula with positiv and negativ counts

bhanu_gautam — Mon, 16 Sep 2024 15:48:42 GMT

Natascha_D , Can you share sample data

Re: Question about DAX Formula with positiv and negativ counts

Natascha_D — Tue, 17 Sep 2024 12:13:00 GMT

bhanu_gautam Here we go 🙂

Re: Question about DAX Formula with positiv and negativ counts

Natascha_D — Thu, 19 Sep 2024 06:38:08 GMT

bhanu_gautam Hello, i want friendly ask have you any idea? BR, Natascha

Re: Question about DAX Formula with positiv and negativ counts

bhanu_gautam — Thu, 19 Sep 2024 07:39:49 GMT

Natascha_D , Please find the attached PBI file and sample data is working on below logic can you check if it is correct or not

resolution time final FM solution time TARGET Expected Result
10 5 5
-8 3 -11
15 7 8
-12 -4 -16
20 10 10
-5 -2 -7

Re: Question about DAX Formula with positiv and negativ counts

Natascha_D — Thu, 19 Sep 2024 10:45:06 GMT

Thank you very much.
The result is not correct. I am trying to make it clearer.
Sum of fm solution time target is always a positive value, which must be subtracted with positive or negative values
e.g. Assigen A = correct
assigne B = incorrect, should be - 5
assigne C = incorrect, should be -8
does this make it clearer? can you help me?

Re: Question about DAX Formula with positiv and negativ counts

bhanu_gautam — Thu, 19 Sep 2024 12:00:48 GMT

In C both are positive than how can it be -8 , I have updated as per logic of B

Re: Question about DAX Formula with positiv and negativ counts

Natascha_D — Thu, 19 Sep 2024 13:11:49 GMT

Already the same problem. the result of the positive values is correct, the negative values are still added in my file

bhanu_gautam

Re: Question about DAX Formula with positiv and negativ counts

Natascha_D — Thu, 19 Sep 2024 13:23:04 GMT

C = my mistake, sorry!