Forum Discussion

Anonymous

Not applicable

5 years ago

Solved

YOY Total with IF Condition on customer value

Hi All,

I need to show the Total Value for each YEAR based on IF condition applied on the customer.

Example for 1 customer in current year:

If (Total Sales = 0 , Last Year Sales, Total Sales)

I have tried the following:

value_last_FY = CALCULATE(SUM(Sales[Value]),SAMEPERIODLASTYEAR('Date'[Date]))

tmp_value = IF(SUM(Sales[Value]) = 0 , [value_last_FY] , SUM(Sales[Value]))

total_value = SUMX('Date', [tmp_value ])

total_value works well when Year & Customer column is added in the table view,

BUT, When I remove Customer column, the total value for each year does not sum up correctly

Thank you in advacne

Tips and Tricks

amitchandak
5 years ago
Anonymous , Please find the file. I have corrected tmp_value. Refer Page 4

I created a new table for customer
PBIX_Issue.pbix181 KB

6 Replies

amitchandak
Super User
5 years ago
Anonymous , you can have

value_last_FY = CALCULATE(SUM(Sales[Value]),SAMEPERIODLASTYEAR('Date'[Date]))
or
value_last_FY = CALCULATE(SUM(Sales[Value]),dateadd('Date'[Date],-1,year))

use isblank

tmp_value = IF( isblank(SUM(Sales[Value])) , [value_last_FY] , SUM(Sales[Value]))

tmp_value = IF( isblank(SUM(Sales[Value])) || SUM(Sales[Value]) =0 , [value_last_FY] , SUM(Sales[Value]))
- Anonymous
  Not applicable
  5 years ago
  amitchandak : Thanks for your response.
  
  Please see the screen shot. total_value measure works well with Year -> Customer -> total_value
  BUT, on the aggregated view the Total does not sum up correctly
  - amitchandak
    Super User
    5 years ago
    Anonymous , you have try like
    
    total_value = SUMX(values('Date'[Date]), [tmp_value ])
    
    total_value = SUMX(values('Date'[Year]), [tmp_value ]) // on second one if first one does work there
    
    That is the row context. So the grand total is calculated again. This will force it take from row totals