- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-21-2018 06:30 AM

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!

Solved! Go to Solution.

Accepted Solutions

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-22-2018 12:13 AM

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

If this post

**helps**, then please consider

**to help the other members find it more quickly.**

*Accept it as the solution*All Replies

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-21-2018 06:55 AM

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!

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-21-2018 07:09 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-21-2018 07:32 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-21-2018 07:59 AM

@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!

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-21-2018 08:29 AM

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 |

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-22-2018 12:13 AM

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

If this post

**helps**, then please consider

**to help the other members find it more quickly.**

*Accept it as the solution*## Re: Multiplying across columns with values as "% of column total"

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-28-2018 05:38 PM

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

If this post

**helps**, then please consider

**to help the other members find it more quickly.**

*Accept it as the solution*## Re: Multiplying across columns with values as "% of column total"

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-29-2018 06:28 AM

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