Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Using factors in IF statement - yields incorrect Totals

Hello experts,

 

I have a question about one of the calculations that I am doing. Suppose, I have 4 products , their grouping and sales value. I have to apply factors for each product. See below table.

 

GroupProductValueFactorValue_Display
1A1001100
1B2000.5100
2C3000.75225
2D4001400
Total 1000 825

 

 

Now, I am using the calculated measure to get the Value_display as below. I get the correct value for the value display but my “Totals” is incorrect. I am getting as “1000” instead of “825”. Also, I have show this in a matrix with hierarchy of Grouping -->Product which is also giving incorrect results.

Value_display =if((SELECTEDVALUE('Table'[Product])="B",0.5*[Value],

IF(SELECTEDVALUE('Table'[Product])="C",0.75*[Value],

[Value]))

 

Any help will be appreciated!

 

Thanks,

Lakshay

  • HotChilli's avatar
    HotChilli
    6 years ago

    Is there a reason why the numbers are hardcoded in the DAX formula when they can be found in the table?

    This measure should work:

    MValue_display = SUMX(TableH, TableH[Factor] * TableH[Value])

1 Reply

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Is there a reason why the numbers are hardcoded in the DAX formula when they can be found in the table?

    This measure should work:

    MValue_display = SUMX(TableH, TableH[Factor] * TableH[Value])