cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ShNBl84 Regular Visitor
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.2019_08_13_08_28_34_Window.jpg
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
ShNBl84 Regular Visitor
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
Community Support Team

Re: Row total grouping only parts of row

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

 

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

Re: Row total grouping only parts of row

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 %

1Monday0.103
2Tuesday0.1136
3Wednesday0.1134
4Thursday0.1259
5Friday0.1946
6Saturday0.2113
7Sunday0.1382

 

ItemSalesDayStoreDay Number

Abc8Monday11
Abc7Tuesday12
Abc Wednesday13
Abc Thursday14
Abc Friday15
Abc Saturday16
Abc Sunday17
Abc7Monday21
Abc7Tuesday22
Abc Wednesday23
Abc Thursday24
Abc Friday25
Abc Saturday26
Abc Sunday27
Abc6.74666666666667Monday31
Abc6.94Tuesday32
Abc Wednesday33
Abc Thursday34
Abc Friday35
Abc Saturday36
Abc Sunday37
ShNBl84 Regular Visitor
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. 

Helpful resources

Announcements
Virtual Launch Event

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.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

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

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 120 members 1,564 guests
Please welcome our newest community members: