cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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 

 

 

 

 

 

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] ) )
        )
    )
)
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" )
        )
)

 

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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 444 members 3,926 guests
Please welcome our newest community members: