cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ZKailar Frequent Visitor
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
Vvelarde Super Contributor
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
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




3 REPLIES 3
mattbrice Senior Member
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 )

 

 

ZKailar Frequent Visitor
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. 

Vvelarde Super Contributor
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
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

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.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 288 members 2,997 guests
Please welcome our newest community members: