Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

DAX for adding columns correctly

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

ARTICLESales Last YearSales this yearsales this year if there is sales both this and LY
A100  
B200600200
C 200 
Sum300800300


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

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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:

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      visheshjain
      Impactful Individual
      3 years ago

      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