cancel
Showing results for
Did you mean:
Frequent Visitor

## Formulas based on sales perecents

I am working on getting the BI formated so i can connect to a server for data instead of using excel. One of my main formulas is using percentages based on sales per day. I believe i need to make a new column, but i can figure out the formula to do so. Here is an example of the type of data i am looking at:

Date              Product       Product Color      Value of units sold           Percent of day sales

8/16/2017     Apples         Red                       \$50                                         50%

8/16/2017     Apples         Green                   \$25                                          25%

8/16/2017     Berries         Black                     \$10                                         10%

8/16/2017     Berries         Blue                      \$15                                          15%

8/17/2017     Apples         Red                       \$40                                          40%

8/17/2017     Apples         Green                   \$35                                           35%

8/17/2017     Berries         Black                     \$5                                            5%

8/17/2017     Berries         Blue                      \$20                                          20%

The last column is what i cannot fiqure out, so any help would be great.

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: Formulas based on sales perecents

@ZKailar

Hi, create a calculated column with:

```PercentofDaySales =
DIVIDE (
Table1[Value of Units Sold],
CALCULATE (
SUM ( Table1[Value of Units Sold] ),
FILTER ( Table1, Table1[Date] = EARLIER ( Table1[Date] ) )
)
)```

Regards

Victor

Lima - Peru

Lima - Peru

Proud to be a Datanaut!

3 REPLIES 3
Senior Member

## Re: Formulas based on sales perecents

Are Date, Product, Product Color all from the same table?  Is Value of units sold a measure; and what table is it derived from?  Are you looking for a measure for Percent of day Sales?  Without some details on model design it is difficult to provide formula needed.

This may work making assumptions about model normalization:

```Percent of day sales =
VAR value_today = [Value of units sold]
VAR all_sales_today =
CALCULATE (
[Value of units sold],
ALL ( Table[Product], Table[Product Color] )
)
RETURN
DIVIDE ( value_today, all_sales_today )```

Frequent Visitor

## Re: Formulas based on sales perecents

Sorry about the confustion. Date, product, product color are from the same table. Value of units sold is also on the same table. I dont think a measure would work (i could be wrong), becasue i need the percentages to stay the same regardless of report filters. I think it just needs to be an additional column. Sorry, im still new to this, excel is what i am used to.

Super Contributor

## Re: Formulas based on sales perecents

@ZKailar

Hi, create a calculated column with:

```PercentofDaySales =
DIVIDE (
Table1[Value of Units Sold],
CALCULATE (
SUM ( Table1[Value of Units Sold] ),
FILTER ( Table1, Table1[Date] = EARLIER ( Table1[Date] ) )
)
)```

Regards

Victor

Lima - Peru

Lima - Peru

Proud to be a Datanaut!

Announcements

#### Community News & Announcements

Get your latest community news and announcements.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 288 members 2,997 guests
Recent signins: