topic Re: wrong measure total including if statement in DAX Commands and Tips

wrong measure total including if statement

Laura1996 — Mon, 09 Sep 2024 05:06:54 GMT

I have a table "Komm", which looks like following. Right now my Total of "Factor final" is wrong and I cant wrap my head arount how to do it correctly:

Name	Product	Description	Amount	Unit	Factor (comes from another table called "Factors")	Factor final
Tom	A	Apple	2	ST	3	6
Jack	B	Rope	30	M	6	6
Jack	C	Apple	5	ST	3	15
Cole	D	Pumpkin	8	KG	4	4

Basically what I want is, that if in my visual I click on the name of the Person, for example Jack he sums up 6 + 15 = 21. So the result is 21. When Jack is not selected it should show the whole sum (over all lines). Additionally to that I have different calculations depending on the Unit, so for "M", "GA", "L" and "KG" he should calculate the Factor * 1, for all the other units the Factor should be multiplied with the Amount.

What I tried is following:

Faktor final =

VAR collecting = SELECTEDVALUE(Komm[Unit])

RETURN

SWITCH(TRUE(),

collecting= "M" || collecting="GA" || collecting="L" || collecting="KG",

SUMX('Factors', 'Factors'[Factor]*1),

SUMX(Komm, Komm[Amount]* RELATED('Factors'[Factor])))

Hope somebody can help me 🙂

Re: wrong measure total including if statement

Irwan — Mon, 09 Sep 2024 05:41:40 GMT

hello Laura1996

please check if this accomodate your need.

create a new measure with following DAX.

Measure = 
var _Name = SELECTEDVALUE('Table'[Name])
Return
IF(
    HASONEVALUE('Table'[Product]),
    SUMX(
        ALLSELECTED('Table'),
        IF(
            'Table'[Unit]="ST",
            'Table'[Amount]*'Table'[Factor (comes from another table called "Factors")],
            'Table'[Factor (comes from another table called "Factors")]
        )
    ),
    SUMX(
        FILTER(
            ALLSELECTED('Table'),
            'Table'[Name]=_Name
        ),
        IF(
            'Table'[Unit]="ST",
            'Table'[Amount]*'Table'[Factor (comes from another table called "Factors")],
            'Table'[Factor (comes from another table called "Factors")]
        )
    )
)

Hope this will help.

Thank you.

Re: wrong measure total including if statement

Laura1996 — Mon, 09 Sep 2024 06:14:31 GMT

hello Irwan 🙂

Unfortunately it doesn't do what it should...as the field "Factor" comes from another table and this table is not mentioned in "ALLSELECTED" he cant find the field. Also ALLSELECTED allows just one argument.

I tried to solve it over related, but its making strange calculations. With my measure they were right and just the total was wrong for the rows selected, now he calculates 6* 0,88 = 3.709,60 which makes no sense. In fact the result is 3709,60 for all the rows.

Also in your Screenshot he calculates 6+3 as 21, when it should be 9. And when you dont select anything he calculates it as 31, which is also not correct...
it actually should be a super simple measure but it became lemon difficult 😄 thank you anyway for trying, hope someone can provide me a working solution

Re: wrong measure total including if statement

Laura1996 — Mon, 09 Sep 2024 06:15:07 GMT

Also I need to focus on this "collecting= "M" || collecting="GA" || collecting="L" || collecting="KG"

the measure shouldn't work with "ST", because there are many more units than just those mentioned up. So when I make an if statement, I have to define which to "exclude" rather than which to consider.

Re: wrong measure total including if statement

Irwan — Mon, 09 Sep 2024 06:32:54 GMT

Wait, you said in your first post, Jack has value of 21 which is 6+15.

I thought 6 (2*3 because ST) and 15 (5*3 because ST), no?

What value for Jack, 9 or 21?

And when not selected, 6 (2*3 ST) + 15 (5*3 ST) + 6 (6*1 M) + 4 (4*1 KG) = 31

Also those M, GA, L, KG in if statement, as far as i can tell, this only for differentiate which will be multiplied and not. I only put ST for multiplying and you can add the rest.

Re: wrong measure total including if statement

Laura1996 — Mon, 09 Sep 2024 06:54:31 GMT

Yes sorry, that was my mistake now because I didn't seen the number 15 in your table. 21 should be correct. Also 31 is correct, however its not working for my data, because my result is always 3709,60. And also in your code I can't add the other table for the Factor (Table "Factor").

Actually trying to add the rest is not working or at least I didn't still figure out how to make it work...

Re: wrong measure total including if statement

Laura1996 — Mon, 09 Sep 2024 06:55:14 GMT

Irwan Anyway - sorry for the confusion 🙂

Re: wrong measure total including if statement

Irwan — Mon, 09 Sep 2024 07:28:12 GMT

Laura1996

How do you collect value from another table?

If it is from a column in another table, you can try adding variable.

I can not test it right now but something like this : var _Factor = SELECTEDVALUE('Table 2'[Factor]).

Then use that variable to replace factor value from DAX above.

Thank you.

Re: wrong measure total including if statement

Irwan — Mon, 09 Sep 2024 07:29:08 GMT

Laura1996

No worries, i thought i misunderstood your requirement.

Re: wrong measure total including if statement

Laura1996 — Mon, 09 Sep 2024 08:41:43 GMT

Irwan thank you for your help. I hope somebody can provide me another solution because whatever I do - with your dax I have way too high calculations and I don't know why.

Thank you anyway for your help

Re: wrong measure total including if statement

Anonymous — Wed, 11 Sep 2024 08:15:23 GMT

Hi Laura1996 ,

Based on the description, try to use the following DAX formula.

Then, select the Jack name.

Best Regards,

Wisdom Wu

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

Re: wrong measure total including if statement

Laura1996 — Tue, 17 Sep 2024 05:59:14 GMT

Hey, thank you for the answer.

Unfortunately something with the calculation still doesn't go right:

Here some results:

The strange thing is that sometimes the calculation is correct and sometimes not. I have no other sums in my visual...so I don't know what it could be... The sum here should be 53,50 considering that I added also M2 into your code... any ideas what it could be?

Re: wrong measure total including if statement

Anonymous — Thu, 19 Sep 2024 09:32:09 GMT

Hi Laura1996 ,
I would like to know how 53.5 is calculated? Which row values are added together to get the result?

What's more, try to check if any other filters are being applied.

Best Regards,

Wisdom Wu

Re: wrong measure total including if statement

Laura1996 — Fri, 20 Sep 2024 12:44:38 GMT

5 M * 10 should equal 10, because thats what the formula says. But it equals 20. The same thing with 6 M.

Because I have to be able to filter also the month / week / day of a persons collected products this triggers the mistake - at least this is what I think. Because when I remove the filter its correct. I never had this problem in any other reports. Is there any way to also include month / week / day into the calculation?

here a screenshot of the date:

the week number I extracted by myself, so I can use it in a calculation. Do I have to do the same stuff for day/month, and how would the code look like, because HASONEVALUE() can work just with one value as far as Im concerned.

Thank you very much

Re: wrong measure total including if statement

Anonymous — Mon, 23 Sep 2024 01:24:41 GMT

Hi Laura1996 ,

Based on the description, try using the following DAX formula.

Faktor final = VAR IsSpecificUnit = SUMX( Komm, IF( Komm[Unit] IN {"M", "GA", "L", "KG"}, RELATED('Factor'[Factor]), Komm[Amount] * RELATED('Factor'[Factor]) ) ) RETURN IF ( HASONEVALUE(Komm[Name]), CALCULATE( SUMX(Komm, Komm[Amount] * RELATED('Factors'[Factor])), FILTER( Komm, Komm[Name] = SELECTEDVALUE(Komm[Name]) && Komm[Month] = SELECTEDVALUE(Komm[Month]) && Komm[Week] = SELECTEDVALUE(Komm[Week]) && Komm[Day] = SELECTEDVALUE(Komm[Day]) ) ), IsSpecificUnit )

Best Regards,

Wisdom Wu

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

Re: wrong measure total including if statement

Laura1996 — Mon, 23 Sep 2024 09:31:51 GMT

hi Anonymous 🙂

well, now its even worse 😄 this is the result:

so now its 2.818,00 instead of 53,50 and it kind of ignores the rule for "M, M2, KG,.."

this report makes me crazy

Re: wrong measure total including if statement

Anonymous — Mon, 23 Sep 2024 10:10:46 GMT

Hi Laura1996 ,

I forget the rule for M, M2. Please try using the following DAX formula.

Faktor final = VAR IsSpecificUnit = SUMX( Komm, IF( Komm[Unit] IN {"M", "GA", "L", "KG"}, RELATED('Factor'[Factor]), Komm[Amount] * RELATED('Factor'[Factor]) ) ) RETURN IF ( HASONEVALUE(Komm[Name]), IF( Komm[Unit] IN {"M", "GA", "L", "KG"}, RELATED('Factor'[Factor]), CALCULATE( SUMX(Komm, Komm[Amount] * RELATED('Factors'[Factor])), FILTER( Komm, Komm[Name] = SELECTEDVALUE(Komm[Name]) && Komm[Month] = SELECTEDVALUE(Komm[Month]) && Komm[Week] = SELECTEDVALUE(Komm[Week]) && Komm[Day] = SELECTEDVALUE(Komm[Day]) ) ), IsSpecificUnit )

Best Regards,

Wisdom Wu

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

Re: wrong measure total including if statement

Laura1996 — Mon, 23 Sep 2024 12:48:25 GMT

hi Anonymous 🙂

unfortunately now dax is telling me that I have too many arguments and that there can be a max. of 3:

it specifically underlines (in the paragraph after the RETURN):

Komm[Unit] IN {"M", "GA", "L", "KG"}, 
            RELATED('Factor'[Factor]),

 IsSpecificUnit

also he's not finding me the variable "Factor" or "Unit" in that if statement
wish I could finally mark this as resolved...but its giving me a headache 😄

Re: wrong measure total including if statement

Nonessential — Mon, 23 Sep 2024 15:06:30 GMT

Hi, try this Laura1996 - Data is the table name so replace with your table name (KOMM) and I have called the Measure 'New Factor' instead of 'Final Factor';

New Factor =

VAR _Tbl =

ADDCOLUMNS(

SELECTCOLUMNS(Data,Data[Name],Data[Description],Data[Amount],Data[Factor (comes from another table called "Factors")],Data[Unit]),

"Final Factor", IF(

Data[Unit] IN {"M", "GA", "L","KG"},

CALCULATE(SUM(Data[Factor (comes from another table called "Factors")])),

CALCULATE(SUMX(Data,Data[Amount]*Data[Factor (comes from another table called "Factors")]))

))

RETURN SUMX(_Tbl,[Final Factor])

Re: wrong measure total including if statement

Laura1996 — Tue, 24 Sep 2024 13:31:51 GMT

hey Nonessential 🙂

thank you for your try, unfortunately, The Factor is not in the same table, so the code is not working. I marked the problem in red, tried i with the relate function or to summarize, however don't know how to make it work:

New Factor =

VAR _Tbl =

ADDCOLUMNS(

SELECTCOLUMNS(Data,Data[Name],Data[Description],Data[Amount],Data[Factor (comes from another table called "Factors")],Data[Unit]),

"Final Factor", IF(

Data[Unit] IN {"M", "GA", "L","KG"},

CALCULATE(SUM(Data[Factor (comes from another table called "Factors")])),

CALCULATE(SUMX(Data,Data[Amount]*Data[Factor (comes from another table called "Factors")]))

))

RETURN SUMX(_Tbl,[Final Factor])