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

[hb_rvp]

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.

Note that I have few variables:

Version - Actual vs. Original Budget/Revision Budget (will be from a selected value in a slicer)

Week and Year - should be dependent on the current ytd i.e. 2024 week 42

Values are from a switch slicer - dynamic value depending on selection. i.e USD sales, Local Sales value, or quantities sold.

just a note that the value should also show even to lowest level of by product id and by customer

[Anonymous · Accepted solution]

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.

[hb_rvp]

Hi This is still not yet resolved 

[hb_rvp]

Sample data: pbix.file 

I created sum measure first for three values:

Base QTY = Quantity Sold

Base USD = USD Sales

Base EUR = EURO Sales

I use this measure to be able to switch between the three: 

Basesales = SWITCH(

    SELECTEDVALUE('slicer_value'[ValueSwitch]),

    "Quantity Sold", [Base QTY],

    "EUR Sales", [Base EUR],

    "USD Sales", [Base USD],

    BLANK()

)

Then Actual formula 

*Actual = CALCULATE([Basesales],'Sales Table'[Version]="Actual")

And Budget formula

*Budget = CALCULATE([Basesales],'Sales Table'[Version]=SELECTEDVALUE('Sales Table'[Version]))

Then a valueswitch slicer is created so user can switch between the three.

slicer_value =

DATATABLE(

    "ValueSwitch", STRING,

    {

        {"Quantity Sold"},

        {"EUR Sales"},

        {"USD Sales"}

    }

)

Then slicer for Version: Actual, Original Budget and Revision Budget

Sample: Revision Budget and USD Sales is selected

Total is correct for Measure column but Value by week is still copying budget

[Thejeswar]

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]

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]

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])

[Thejeswar]

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]

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

[hb_rvp]

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.

[Kedar_Pande]

Corrected Total = 
SUMX(
VALUES('YourTable'[Week #]),
IF(
ISBLANK([YTD TY Actual]),
[YTD TY Budget],
[YTD TY Actual]
)
)

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn