Forum Discussion

hb_rvp's avatar
hb_rvp
Frequent Visitor
1 year ago
Solved

subtotals not calculating correctly where formula picks up budget sales if actual is null

Weekly Sales sample   I basically just did an if condition   IF(     ISBLANK([YTD TY Actual]),     [YTD TY Budget],     [YTD TY Actual]))   Please help what should be the correct one. ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi hb_rvp 

     

    Please try this:

    Here I change the *NewColumn measure:

    *NewColumn =
SUMX (
    SUMMARIZE ( 'Sales Table', [Month], [Day] ),
    IF ( ISBLANK ( [*Actual] ), [*Budget], [*Actual] )
)

    The result is as follow:

     

     

    Best Regards

    Zhengdong Xu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thejeswar's avatar
Thejeswar
Super User
1 year ago

Hi hb_rvp ,

I would suggest you to create a new column having this IF statement. Then have a measure with a SUMX() DAX function

NewColumn = IF(ISBLANK(Table_[Actual]), Table_[Budget], Table_[Actual])
Measure = SUMX(Table_, Table_[NewColumn])

See screenshot below

 

hb_rvp's avatar
hb_rvp
Frequent Visitor
1 year ago

Hey Thank you Thejeswar Kedar_Pande I've tried both ways

 

 

it seems both your suggestions, captured the correct sum of total. but the weekly values are capturing the budget only and not considering the actual sales for week 37, 38, and 42.

 

 

 

  • Thejeswar's avatar
    Thejeswar
    Super User
    1 year ago

    Hi hb_rvp ,

    The Below formula that you shared works that way.

    Whenever your actual is blank for a week, it pulls up the Budget value in place. 

    IF(

        ISBLANK([YTD TY Actual]),

        [YTD TY Budget],

        [YTD TY Actual]))

     

    If your requirement is different, do share the same here

     

    Regards,

    • hb_rvp's avatar
      hb_rvp
      Frequent Visitor
      1 year ago

      Hi Thejeswar 

       

      New Columns' weekly values are correct. It picked up budget on weeks with blank actuals. but total is incorrect.

       

      Then measure is a correct sum total of budget but weekly values are not picking up what its supposed to be

       

      Are you able to suggest, how can i then show this to a matrix if one or the other is wrong.

       

       

    • hb_rvp's avatar
      hb_rvp
      Frequent Visitor
      1 year ago

      Thejeswar Please can you look at my last message for the pbix test file thank you

  • Thejeswar's avatar
    Thejeswar
    Super User
    1 year ago

    Hi hb_rvp ,

    Did you use the New Column that you created as part of your measure.? I think your measure is wrong, that is why your measure is showing all budget values. 
    When your NewColumn that created has right values, your measure won't take values from Budget. The Below is the formula that you should use to get the sum total of the New Column

    Measure = SUMX(Table_, Table_[NewColumn])