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

 Date Sales 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?

 Date Sales Monthly 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

## 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
9 REPLIES 9
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
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 Sales Estimated 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

## 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
Super User

## Re: New Column with Total Sales

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

```New Table =
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
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 2017 Sales \$2,600 October 2017 Operations \$500 September 2017 Sales \$3,100 September 2017 Operations \$750 August 2017 Sales \$1,000 August 2017 Operations \$300 Table 2 Actual Sales October 2017 1900 October 2017 1200 September 2017 1000 September 2017 900 August 2017 2000 August 2017 1500 Sales Variance (Desired Outcome) October 2017 \$                               500 September 2017 \$                         (1,200) August 2017 \$                           2,500
Super User

## Re: New Column with Total Sales

@Allan77R2V1

Check this file

Following formula is used for the New / desired table

```NewTable =
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

## 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
Regular Visitor

## Re: New Column with Total Sales

Many thanks for your help. This worked perfectly.

Highlighted
Super User

## Re: New Column with Total Sales

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

#### Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)