Column subtotal only includes "TRUE" portion of measure's IF statement

[ShNBl84]

I have a measure that says if a day has actual sales, then show the average of sales, otherwise show "1": "

pred = IF(SUM(Sheet1[Sales])>0, AVERAGE(Sheet1[Sales]),1)"

The column total only uses the "TRUE" portion of the IF statement. I have a fairly complicated measure instead of "1" in my real data that is meant to predict the remaining days' sales, but my total only shows the average of actual sales.

Any insights on how to get the Total to average all columns? I do not see a calculated column as a solution due to the complicated nature of the actual measure.

Thanks!

[Anonymous]

@ShNBl84  - If I understand correctly, you want to get the sales for each day, and then take the average of those values. Please note that is different from taking the average value of the entire set. The total is currently doing the latter, as it is simply the same calculation without the filter on day. 

To average for each day, and then average the daily value, you can do something like this:

pred = 
var _sum = SUMMARIZE(
    Sheet1,
    [DayOfWeek],
    "Avg",IF(
        SUM(Sheet1[Sales])>0, 
        AVERAGE(Sheet1[Sales]),
        1
    )
)
return AVERAGEX(_sum,[Avg])

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.

[ShNBl84]

Thanks for the time and the new techniques to study. The formula works for items with actual sales, but when I put in my measure instead of "1", I only get blanks. I am not getting blanks in my current format, but the column total is ignoring the values. 😕

[Anonymous]

what is the measure?

[ShNBl84]

Here is the measure: PlatesPred = [IndSumPlates]/[IndSum%]*[IndDaily%]

Below are the measures used in the above measure:

The DailyIndices table is our estimate of what % of plates are sold by day of week. For example, Monday would be 13%.

IndSumPlates =

VAR Mon = CALCULATE(AVERAGE(PlateSales[PlatesSold]), ALL(DayOfWeek), DailyIndices[Day]="Monday")

VAR Tues = CALCULATE(AVERAGE(PlateSales[PlatesSold]), ALL(DayOfWeek), DailyIndices[Day]="Tuesday")

VAR Wed = CALCULATE(AVERAGE(PlateSales[PlatesSold]), ALL(DayOfWeek), DailyIndices[Day]="Wednesday")

VAR Thur = CALCULATE(AVERAGE(PlateSales[PlatesSold]), ALL(DayOfWeek), DailyIndices[Day]="Thursday")

VAR Fri = CALCULATE(AVERAGE(PlateSales[PlatesSold]), ALL(DayOfWeek), DailyIndices[Day]="Friday")

VAR Sat = CALCULATE(AVERAGE(PlateSales[PlatesSold]), ALL(DayOfWeek), DailyIndices[Day]="Saturday")

RETURN

Mon + IF(MAX(DailyIndices[Day]) = "Wednesday", Tues, IF(MAX(DailyIndices[Day]) = "Thursday", Tues+Wed, IF(MAX(DailyIndices[Day]) = "Friday", Tues+Wed+Thur,IF(MAX(DailyIndices[Day]) = "Saturday", Tues+Wed+Thur+Fri,IF(MAX(DailyIndices[Day]) = "Sunday", Tues+Wed+Thur+Fri+Sat)))))

IndSum% =

VAR Mon = CALCULATE(AVERAGE(DailyIndices[Value]), ALL(DayOfWeek), DailyIndices[Day]="Monday")

VAR Tues = CALCULATE(AVERAGE(DailyIndices[Value]), ALL(DayOfWeek), DailyIndices[Day]="Tuesday")

VAR Wed = CALCULATE(AVERAGE(DailyIndices[Value]), ALL(DayOfWeek), DailyIndices[Day]="Wednesday")

VAR Thur = CALCULATE(AVERAGE(DailyIndices[Value]), ALL(DayOfWeek), DailyIndices[Day]="Thursday")

VAR Fri = CALCULATE(AVERAGE(DailyIndices[Value]), ALL(DayOfWeek), DailyIndices[Day]="Friday")

VAR Sat = CALCULATE(AVERAGE(DailyIndices[Value]), ALL(DayOfWeek), DailyIndices[Day]="Saturday")

RETURN

Mon + IF(MAX(DailyIndices[Day]) = "Wednesday", Tues, IF(MAX(DailyIndices[Day]) = "Thursday", Tues+Wed, IF(MAX(DailyIndices[Day]) = "Friday", Tues+Wed+Thur,IF(MAX(DailyIndices[Day]) = "Saturday", Tues+Wed+Thur+Fri,IF(MAX(DailyIndices[Day]) = "Sunday", Tues+Wed+Thur+Fri+Sat)))))

IndDaily% =

VAR Tues = CALCULATE(AVERAGE(DailyIndices[Value]), ALL(DayOfWeek), DailyIndices[Day]="Tuesday")

VAR Wed = CALCULATE(AVERAGE(DailyIndices[Value]), ALL(DayOfWeek), DailyIndices[Day]="Wednesday")

VAR Thur = CALCULATE(AVERAGE(DailyIndices[Value]), ALL(DayOfWeek), DailyIndices[Day]="Thursday")

VAR Fri = CALCULATE(AVERAGE(DailyIndices[Value]), ALL(DayOfWeek), DailyIndices[Day]="Friday")

VAR Sat = CALCULATE(AVERAGE(DailyIndices[Value]), ALL(DayOfWeek), DailyIndices[Day]="Saturday")

VAR Sun = CALCULATE(AVERAGE(DailyIndices[Value]), ALL(DayOfWeek), DailyIndices[Day]="Sunday")

RETURN

IF(MAX(DailyIndices[Day]) = "Tuesday", Tues, IF(MAX(DailyIndices[Day]) = "Wednesday", Wed, IF(MAX(DailyIndices[Day]) = "Thursday", Thur, IF(MAX(DailyIndices[Day]) = "Friday", Fri, IF(MAX(DailyIndices[Day]) = "Saturday", Sat, IF(MAX(DailyIndices[Day]) = "Sunday", Sun))))))

[AlB]

@ShNBl84 

I haven't really looked in detail at what the problem might be but, based in @Anonymous's solution, try this variant:

pred =
VAR _sum =
    ADDCOLUMNS (
        SUMMARIZE ( Sheet1, [DayOfWeek] ),
        "Avg", IF ( SUM ( Sheet1[Sales] ) > 0, AVERAGE ( Sheet1[Sales] ), 1 )
    )
RETURN
    AVERAGEX ( _sum, [Avg] )

where we use ADDCOLUMNS rather than summarize to create "Avg". SUMMARIZE introduces both row and filter contexts (and other behind-the-scenes behavior) that might give rise to issues in cases like this. ADDCOLUMNS has only a row context. No idea if it will work but it's worth a quick try. 

[ShNBl84]

Much appreciated @AlB, but still getting blanks for my FALSE/predicted sales.

[Anonymous]

@ShNBl84 -

I noticed you have 2 different tables. Try the following, which summarizes based off of the dimension table, instead of the fact table:

pred = 
var _sum = SUMMARIZE(
    DayOfWeek,
    [DayOfWeek],
    "Avg",IF(
        SUM(DailyIndices[Sales])>0, 
        AVERAGE(DailyIndices[Sales]),
        1
    )
)
return AVERAGEX(_sum,[Avg])

Another possible additional change is to replace all instances of filtering on the fact table with a filter on the dimension table. For example, replace this:

VAR Tues = CALCULATE(AVERAGE(DailyIndices[Value]), ALL(DayOfWeek), DailyIndices[Day]="Tuesday")

with this:

VAR Tues = CALCULATE(AVERAGE(DailyIndices[Value]), ALL(DayOfWeek), DayOfWeek[Day]="Tuesday")

[ShNBl84]

I appreciate the efforts. I simply keep getting blanks in the predictive cells. To get the subtotal data, I resorted to making a separate table with a new measure that calculated the total.

[Anonymous]

Hi ShNBl84, would it be possible for you to post a PBIX file that contains a bit of sample data to play with? It's much easier to help troubleshoot with some data (even if "dummy" data)

I feel like there are two things going on:

1) Strongly feel you need to use SUMX or AverageX to iterate over days

2) Not sure why you are building a measure with hard coded monday, tuesday, etc. in it. If the measure is built properly it should take that into account if you display it by day of the week

Unfortunately only have limited time to help, much better spent if you could upload a bit of sample data and show what the results are you're looking for.

Thx,

Scott

[Anonymous]

Oh, I see, you seem to be doing a Week to date type calculation I think. Definitely would be better to not hard code specific days into the formula.

Thx,

Scott