Forum Discussion
wrong measure total including if statement
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 =
Hope somebody can help me 🙂
Hi Laura1996 ,
Based on the description, try to use 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]), IsSpecificUnit, SUMX( Komm, IF ( Komm[Unit] IN {"M", "GA", "L", "KG"}, RELATED('Factor'[Factor]), Komm[Amount] * RELATED('Factor'[Factor]) ) ) )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.
22 Replies
Hi Laura1996 ,
Based on the description, try to use 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]), IsSpecificUnit, SUMX( Komm, IF ( Komm[Unit] IN {"M", "GA", "L", "KG"}, RELATED('Factor'[Factor]), Komm[Amount] * RELATED('Factor'[Factor]) ) ) )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.
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?
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
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.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 solutionAlso 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.
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])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])Hi Laura1996 , Could you bring the Factor from your Factors table into your KOMM table using a calculated column? Something like;
LOOKUPVALUE(
'Factors'[Factor],'KOMM'[Description],'Factors'[Description])This assumes that there are 2 matching/correlating columns, one in KOMM and another in Factors - in the example above the matching columns are Description but you could swap these out if necessary.
