Multiplying across columns with values as "% of column total"

[danb]

I am trying to build a matrix that shows item sales by day of week.

The columns should be as follows:

1) expected % of items sold by day of week (done!)

2) # of actual items sold (done!)

3) % of actual items sold by day (done!)

4) # of items forecasted to be sold this week (close enough)

5) # of items expected to be sold based on actual # of items sold (help!)

For example, I need to know how many items I should expect to sell on Thursday (which is statistically 12% of my weekly sales) based on the actual sales from Monday and Tuesday. Part of the problem is that the % columns are in "% of column total" format, so I can't simply create a measure that multiplies across columns.

Thanks!

[Greg_Deckler]

So in your visual below, what are you trying to calculate? Forecasted? In psuedo-code, what would the calculation be for #5? Sample data would be tremendous.

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[danb]

Thanks for the reply.

The Actual column would only be filled on Monday - Tuesday because today is Wednesday. To find Thursday's projected # of items sold, I would use the sum of the % of Monday - Tuesday (21%) and the sum of the items (14k) and calculate the # of expected items sold on Thursday.

So, if 21% of my week's sales is 14k items, what will 12% of my sales be?

14,000/.21 = x/.12

or

# Thursday's expected sales  = sum # of sales this Mon-Tues / sum % of days Mon-Tues * percentage expected sales for Thursdays

[danb]

@Greg_Deckler - responded to your question above but forgot to tag you. Thanks!

[Greg_Deckler]

@danb - Still going to need some sample source in text to work this one through.

[danb]

@Greg_Deckler

Sales Date	# Items Sold	Day Of Week
8/23/2018	10	Thursday
8/24/2018	24	Friday
8/25/2018	9	Saturday
8/26/2018	6	Sunday
8/27/2018	4	Monday
8/28/2018	5	Tuesday
8/29/2018	7	Wednesday
8/30/2018	8	Thursday
8/31/2018	9	Friday
9/1/2018	6	Saturday
9/2/2018	11	Sunday
9/3/2018	1	Monday
9/5/2018	2	Wednesday
9/6/2018	2	Thursday
9/7/2018	2	Friday
9/8/2018	2	Saturday
9/9/2018	24	Sunday
9/10/2018	2	Monday
9/11/2018	8	Tuesday
9/12/2018	6	Wednesday
9/13/2018	9	Thursday
9/14/2018	5	Friday
9/15/2018	4	Saturday
9/16/2018	4	Sunday
9/17/2018	4	Monday
9/18/2018	2	Tuesday
9/19/2018	6	Wednesday
9/20/2018	2	Thursday
9/21/2018	6	Friday
9/22/2018	18	Saturday
9/23/2018	15	Sunday
9/24/2018	4	Monday
9/26/2018	2	Wednesday
9/27/2018	1	Thursday
9/28/2018	5	Friday
9/30/2018	7	Sunday
10/1/2018	2	Monday
10/2/2018	3	Tuesday
10/3/2018	4	Wednesday
10/4/2018	3	Thursday
10/5/2018	3	Friday
10/6/2018	3	Saturday
10/7/2018	9	Sunday
10/10/2018	7	Wednesday
10/11/2018	5	Thursday
10/12/2018	10	Friday
10/13/2018	6	Saturday
10/14/2018	14	Sunday
10/15/2018	3	Monday
10/16/2018	2	Tuesday
10/17/2018	3	Wednesday
10/18/2018	7	Thursday
10/19/2018	7	Friday
10/20/2018	3	Saturday
10/21/2018	8	Sunday
10/22/2018	9	Monday
10/23/2018	5	Tuesday
10/25/2018	3	Thursday
10/26/2018	10	Friday
10/27/2018	3	Saturday
10/28/2018	10	Sunday
10/29/2018	2	Monday
10/30/2018	2	Tuesday
10/31/2018	3	Wednesday
11/1/2018	3	Thursday
11/2/2018	3	Friday
11/3/2018	1	Saturday
11/4/2018	14	Sunday

[v-cherch-msft]

Hi @danb

Could you mark my answer as a solution if you feel that makes sense? If not, please share more details for your scenario so that we could help further on it.

Regards,

Cherie

[danb]

@v-cherch-msft - sorry for the delay. Yes the solution that was offered does meet the problem. Thank you for your help!

[v-cherch-msft · Accepted solution]

Hi @danb

You may check if below measure could make sense.

Thursday's expected sales =
CALCULATE (
    [Actual] / [Actual % by Day],
    FILTER ( Table, Table[Day Of Week] IN { "Monday", "Tuesday" } )
)
    * CALCULATE ( [Exp % by Day], FILTER ( Table, Table[Day Of Week] = "Thursday" ) )

Regards,

Cherie