cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Allan77R2V1 Regular Visitor
Regular Visitor

New Column with Total Sales

I can't seem to figure this out and looking for some guidance. I have the following table

DateSales
October 2017$600
October 2017$300
October 2017$1,000
September 2017$600
September 2017$500
September 2017$1,300
August 2017$2,200
August 2017$250

 

I want to add a 3rd column that adds the monthly sales. Any suggestions?

DateSalesMonthly Total
October 2017$600$1,900
October 2017$300 
October 2017$1,000 
September 2017$600$2,400
September 2017$500 
September 2017$1,300 
August 2017$2,200$2,450
August 2017$250 

 

 

 

 

 

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: New Column with Total Sales

Hi @Allan77R2V1,

 

You may refer to my solution here.

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

9 REPLIES 9
Super User
Super User

Re: New Column with Total Sales

Hi,

 

If your objective is to see the Monthly Total in a visual, just drag the Date column to your visual and write a simple SUM measure to add the numbers in the Sales column.  Why do you need a Monthly Total column in your base data table?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Allan77R2V1 Regular Visitor
Regular Visitor

Re: New Column with Total Sales

Hi Ashish,

 

I didnt provide my end goal as I thought by getting the answer would point me in the right direction.  So here is my end goal. I also have another table similar to the on below. It also only has the first two columns. My goal is to calculate the the difference of estimated and actual sales by month and then in the visualizer graph the difference by month.

 

Date

Estimate SalesEstimated Monthly Total
October 2017$900$2,100
October 2017$400 
October 2017$800 
September 2017$200$2,700
September 2017$1,000 
September 2017$1,500 
August 2017$1,800$2,300
August 2017$500 

 

 

 

 

 

Highlighted
Super User
Super User

Re: New Column with Total Sales

Hi,

 

To accomplish that, we do not need to do what you originally asked for.  Share both datasets and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Super User
Super User

Re: New Column with Total Sales

Hi @Allan77R2V1

 

Go to Modelling Tab, press the NEW TABLE button and enter this formula

 

New Table =
ADDCOLUMNS (
    SUMMARIZE ( TableName, TableName[Date], TableName[Sales] ),
    "Monthly Total", IF (
        TableName[Sales]
            = CALCULATE (
                FIRSTNONBLANK ( TableName[Sales], TableName[Sales] ),
                FILTER ( ALL ( TableName ), TableName[Date] = EARLIER ( TableName[Date] ) )
            ),
        CALCULATE (
            SUM ( TableName[Sales] ),
            FILTER ( ALL ( TableName ), TableName[Date] = EARLIER ( TableName[Date] ) )
        )
    )
)
Try my new Power BI game Cross the River
Allan77R2V1 Regular Visitor
Regular Visitor

Re: New Column with Total Sales

Below are samples of the date and the desired outcome. Table 1 is the estimated sales and operations. It will have one line per month for each item. I need to some how indentify the sales row for each month.

 

The second table will have several lines per monthn and the number of lines per month can vary. I need to add each month up to get my actual sales

 

The last table is the desired out come. It has on line per month and it has subtracted the monthly estimated sales from the actual sales

   
Table 1  
Estimated Sales and Operations  
October 2017Sales$2,600
October 2017Operations$500
September 2017Sales$3,100
September 2017Operations$750
August 2017Sales$1,000
August 2017Operations$300
   
Table 2  
Actual Sales  
October 20171900 
October 20171200 
September 20171000 
September 2017900 
August 20172000 
August 20171500 
   
   
   
Sales Variance (Desired Outcome) 
October 2017$                               500 
September 2017$                         (1,200) 
August 2017$                           2,500 
   
Super User
Super User

Re: New Column with Total Sales

@Allan77R2V1

 

Check this file

 

Following formula is used for the New / desired table

 

NewTable =
ADDCOLUMNS (
    SUMMARIZE ( Table2, Table2[Month] ),
    "Difference", CALCULATE ( SUM ( Table2[Amount] ) )
        - CALCULATE (
            SUM ( Table1[Amount] ),
            FILTER ( Table1, Table1[Month] = Table2[Month] && Table1[Type] = "Sales" )
        )
)

 

Try my new Power BI game Cross the River
Super User
Super User

Re: New Column with Total Sales

Hi @Allan77R2V1,

 

You may refer to my solution here.

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Allan77R2V1 Regular Visitor
Regular Visitor

Re: New Column with Total Sales

Many thanks for your help. This worked perfectly.

 

Super User
Super User

Re: New Column with Total Sales

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)