Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
danb
Resolver I
Resolver I

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

1 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

 

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

8 REPLIES 8
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

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

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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

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.

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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.