cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Natantis
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

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

1 ACCEPTED SOLUTION

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

Did I answer your question?
If yes, then please mark my post as a solution!

Thank you,
Vishesh Jain

Proud to be a Super User!



View solution in original post

4 REPLIES 4
Natantis
Frequent Visitor

Thank you!

Natantis
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

 

 

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

Did I answer your question?
If yes, then please mark my post as a solution!

Thank you,
Vishesh Jain

Proud to be a Super User!



PowerUserR
Solution Supplier
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:
PowerUserR_0-1662118732079.png

 

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors