## Desktop

Member
Posts: 46
Registered: ‎10-03-2017

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

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!

Accepted Solutions
Community Support Team
Posts: 1,343
Registered: ‎07-25-2018

## Re: Multiplying across columns with values as "% of column total"

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

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

All Replies
Super User
Posts: 10,478
Registered: ‎07-11-2015

## Re: Multiplying across columns with values as "% of column total"

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Member
Posts: 46
Registered: ‎10-03-2017

## Re: Multiplying across columns with values as "% of column total"

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

Member
Posts: 46
Registered: ‎10-03-2017

## Re: Multiplying across columns with values as "% of column total"

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

Super User
Posts: 10,478
Registered: ‎07-11-2015

## Re: Multiplying across columns with values as "% of column total"

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Member
Posts: 46
Registered: ‎10-03-2017

## Re: Multiplying across columns with values as "% of column total"

@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
Community Support Team
Posts: 1,343
Registered: ‎07-25-2018

## Re: Multiplying across columns with values as "% of column total"

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Posts: 1,343
Registered: ‎07-25-2018

## Re: Multiplying across columns with values as "% of column total"

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member
Posts: 46
Registered: ‎10-03-2017

## Re: Multiplying across columns with values as "% of column total"

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