cancel
Showing results for
Did you mean:
Frequent Visitor

## 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

 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

1 ACCEPTED SOLUTION
Super User

Hi @Natantis,

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

If yes, then please mark my post as a solution!

Thank you,
Vishesh Jain

Proud to be a Super User!

4 REPLIES 4
Frequent Visitor

Thank you!

Frequent Visitor

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

Super User

Hi @Natantis,

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

If yes, then please mark my post as a solution!

Thank you,
Vishesh Jain

Proud to be a Super User!

Solution Supplier

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: