Reply
Member
Posts: 46
Registered: ‎10-03-2017
Accepted Solution

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!

 

PBI help.png


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.

View solution in original post


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 SoldDay Of Week
8/23/201810Thursday
8/24/201824Friday
8/25/20189Saturday
8/26/20186Sunday
8/27/20184Monday
8/28/20185Tuesday
8/29/20187Wednesday
8/30/20188Thursday
8/31/20189Friday
9/1/20186Saturday
9/2/201811Sunday
9/3/20181Monday
9/5/20182Wednesday
9/6/20182Thursday
9/7/20182Friday
9/8/20182Saturday
9/9/201824Sunday
9/10/20182Monday
9/11/20188Tuesday
9/12/20186Wednesday
9/13/20189Thursday
9/14/20185Friday
9/15/20184Saturday
9/16/20184Sunday
9/17/20184Monday
9/18/20182Tuesday
9/19/20186Wednesday
9/20/20182Thursday
9/21/20186Friday
9/22/201818Saturday
9/23/201815Sunday
9/24/20184Monday
9/26/20182Wednesday
9/27/20181Thursday
9/28/20185Friday
9/30/20187Sunday
10/1/20182Monday
10/2/20183Tuesday
10/3/20184Wednesday
10/4/20183Thursday
10/5/20183Friday
10/6/20183Saturday
10/7/20189Sunday
10/10/20187Wednesday
10/11/20185Thursday
10/12/201810Friday
10/13/20186Saturday
10/14/201814Sunday
10/15/20183Monday
10/16/20182Tuesday
10/17/20183Wednesday
10/18/20187Thursday
10/19/20187Friday
10/20/20183Saturday
10/21/20188Sunday
10/22/20189Monday
10/23/20185Tuesday
10/25/20183Thursday
10/26/201810Friday
10/27/20183Saturday
10/28/201810Sunday
10/29/20182Monday
10/30/20182Tuesday
10/31/20183Wednesday
11/1/20183Thursday
11/2/20183Friday
11/3/20181Saturday
11/4/201814Sunday
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!