cancel
Showing results for
Did you mean:
Regular Visitor

## Row total grouping only parts of row

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Regular Visitor

## Re: Row total grouping only parts of row

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.

3 REPLIES 3
Community Support Team

## Re: Row total grouping only parts of row

Hi @ShNBl84 ,

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

## Re: Row total grouping only parts of row

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
Regular Visitor

## Re: Row total grouping only parts of row

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.

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### PBI Community Highlights

Check out what's new in the Power BI Community!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 120 members 1,564 guests
Recent signins: