Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ZKailar
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
Vvelarde
Community Champion
Community Champion

@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

View solution in original post

3 REPLIES 3
Vvelarde
Community Champion
Community Champion

@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
mattbrice
Solution Sage
Solution Sage

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 )

 

 

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.