DAX for adding columns correctly

[Anonymous]

Hi, 

I have an Issue with getting the correct sum in a matrix. I think I neet to use sumx in some way but I am unable to get it working

ARTICLE	Sales Last Year	Sales this year	sales this year if there is sales both this and LY
A	100
B	200	600	200
C		200
Sum	300	800	300

I am trying to get the red text to be 200. the formula I am using is "sales this year if there is sales both this and LY = IF([Local_fsg Last Year] = 0, BLANK(),  [Local_fsg Current])"

I understand why it is not working but no mater what I do I either get the sum to be empty or it says 300 (as above)

can anyone please point me in the right direction? 
thanks

g

[Anonymous]

Thank you!

[Anonymous]

Hi there PowerUser, 

Thanks for taking time to look at my issue. 

what I am lookin to do is to force the sum row to actually calculate the sum of the column. The value 200 I get in "sales this year if there is sales both this and LY" is correct but i want the sum row to actually do a sum, not the same formula as the detail rows

Is this more clear? 

thanks and regards 
G

[visheshjain · Accepted solution]

Hi @Anonymous,

Please have a look at the following video, it should be exactly what you are looking for.

Computing totals by summing visible values in DAX - YouTube

Hope this helps and if it solves your problem, then please do mark it as a solution so that others can reach the solution faster.

Thank you,

Vishesh Jain

[PowerUserR]

Hi,

I am not sure that I understand the mathematical logic behind what you are trying to achieve here. 
However, based on the name of your column, this would generate the answer:

Sales this year given sales this and last year =

SUMX (

    FILTER (

        'Table (3)',

        EARLIER ('Table (3)'[ARTICLE] ) = 'Table (3)'[ARTICLE]

            && 'Table (3)'[Sales Last Year] <> 0

    ),

    'Table (3)'[Sales this year]


This might be a missunderstanding from my side thought as this returns: