Row total grouping only parts of row

[ShNBl84]

The total row is only getting the average of part of the row.

My measure is:

Sales Actual Or Predicted = IF(SUM(ItemSales[Sold])>0,AVERAGE(ItemSales[Sold]),[Predicted Sales])

In this case, we have actual sales Monday and Tuesday but today is Wednesday so we have only predicted sales for the rest of the week. Conditional formatting shows which days do not have actual sales. My row total is only counting days of actual sales when calculating the weekly average. So I think I need to adjust my predicted sales measures.

We get predictions by using the % of expected sales by day of week and using the first day(s)' sales to predict the rest of the week. So if Monday (10.3%) and Tuesday (11.2%) combined is expected to be 21.5% of our weekly sales and Wednesday is expected to be 11.5%, then (7.08+6.94)/21.5*11.5 = 7.34

My [Predicted Sales] measure is:

Predicted Sales = [Index Sum Sales]/[Index % Sum]*[Index % by Day]

with Mon, Tues, Wed, etc. equaling the % of sales expected that week.

Index Sum Sales=

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

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

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

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

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

VAR Sat = CALCULATE(AVERAGE(ItemSales[Sold]), 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)))))

Index % Sum=

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

Index % by Day =

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

You help is much appreciated!

[v-piga-msft]

Hi @ShNBl84 ,

I have a little confused about your requirement.

What do you want to achieve in power bi? Is the image your desired output?

In addition, how do you predict that  the sales for Wednesday is 11.5%?

If it is convenient, could you share your data sample with table format and the desired output so that we could have a test on it?

Best  Regards,

Cherry

[ShNBl84]

Thanks for your reply.

The image is my current output that has the total only including the days of actual sales. I want it to include all the days (even the predicted sales).

For the sales percentages, those are hard coded values.

Below is some sample data:

NumberDayWeekly Sales %

1	Monday	0.103
2	Tuesday	0.1136
3	Wednesday	0.1134
4	Thursday	0.1259
5	Friday	0.1946
6	Saturday	0.2113
7	Sunday	0.1382

ItemSalesDayStoreDay Number

Abc	8	Monday	1	1
Abc	7	Tuesday	1	2
Abc		Wednesday	1	3
Abc		Thursday	1	4
Abc		Friday	1	5
Abc		Saturday	1	6
Abc		Sunday	1	7
Abc	7	Monday	2	1
Abc	7	Tuesday	2	2
Abc		Wednesday	2	3
Abc		Thursday	2	4
Abc		Friday	2	5
Abc		Saturday	2	6
Abc		Sunday	2	7
Abc	6.74666666666667	Monday	3	1
Abc	6.94	Tuesday	3	2
Abc		Wednesday	3	3
Abc		Thursday	3	4
Abc		Friday	3	5
Abc		Saturday	3	6
Abc		Sunday	3	7

[ShNBl84 · Accepted solution]

I discovered that column/row subtotals do not work on the FALSE section of IF statements. For example, the subtotal of "=IF("any day before today", 10, 5)" will always be 10, even if a date includes a day after today. Current solution I am exploring is using a calculated column to do the measure's IF statement.