Percentage of daily sales

[alexcatala]

Hi, I am trying to figure it out the percentage of daily sales. I calculated the total of the week but can't find the correct measure to extract the daily percentage of sales from the total of the week.

I am just trying to display the percentage of each day from the total of the week.The formulas I have are the followings:

Daily sales: 

Sales (D) =

VAR BoP_Date_Fixed = [BoP Date]

VAR d_StoresAuto_Enriched =

ADDCOLUMNS (

d_StoresAuto,

"Include", MAX (

[COMP_Active] * ( [OpeningDateAniversary] <= BoP_Date_Fixed ),

[NEW_Active] * ( [OpeningDateAniversary] > BoP_Date_Fixed )

)

)

VAR d_StoresAuto_Enriched_Filtered =

FILTER ( d_StoresAuto_Enriched, [Include] = 1 )

VAR Result =

SUMX (

d_StoresAuto_Enriched_Filtered,

CALCULATE ( SUM ( f_DailySales[Ext. Price] ) )

)

RETURN

IF ( Result = 0, [ND_Sign], Result )

Weekly sales:

Sales (W) =

VAR BoP_Date_Fixed = [BoP Date]

VAR f_WeeklyFigures_Enriched =

ADDCOLUMNS (

FILTER(f_WeeklyFigures,f_WeeklyFigures[Measure] = "Sales" && f_WeeklyFigures[Value] > 0),

"Include", MAX (

[COMP_Active] * ( [OpeningDateAniversary] <= BoP_Date_Fixed ),

[NEW_Active] * ( [OpeningDateAniversary] > BoP_Date_Fixed )

)

)

VAR f_WeeklyFigures_Enriched_Filtered =

Filter(f_WeeklyFigures_Enriched, [Include] = 1)

VAR Result =

SUMX (

f_WeeklyFigures_Enriched_Filtered,

[Value]

)

RETURN

IF ( Result = 0, [ND_Sign], Result )

and the date formula I am using for the day of the week:

d_DateTable =

VAR EarliestDate =

MIN ( MIN ( f_DailySales[Date] ), MIN ( f_Employees[EmpLeavDate] ) )

VAR LatestDate =

MAX ( MAX ( f_DailySales[Date] ), MAX ( f_Employees[EmpJoinDate] ) )

VAR StartDate =

DATE ( YEAR ( EarliestDate ), 1, 1 )

VAR EndDate =

DATE ( YEAR ( LatestDate ), MONTH ( LatestDate ) + 1, 1 ) - 1

RETURN

ADDCOLUMNS (

CALENDAR ( StartDate, EndDate ),

"Year", YEAR ( [Date] ),

"Quarter", QUARTER ( [Date] ),

"Month", MONTH ( [Date] ),

"Week", WEEKNUM ( [Date], 1 ),

"Year_Quarter", YEAR ( [Date] ) & "_Q"

& QUARTER ( [Date] ),

"Year_Month", YEAR ( [Date] ) & "_M"

& FORMAT ( MONTH ( [Date] ), "00" ),

"Year_WeekNum", YEAR ( [Date] ) & "_W"

& FORMAT ( WEEKNUM ( [Date], 1 ), "00" ),

"Day_Week",WEEKDAY([Date],2),

"Year_WeekNum (Skechers)",

VAR SkWeek =

LOOKUPVALUE ( d_SkechersWeeks[Skechers Week], d_SkechersWeeks[Date], [Date] )

VAR LastSkDay =

MAX ( d_SkechersWeeks[Date] )

VAR WeekNum_Bias =

VALUE (

RIGHT (

LOOKUPVALUE ( d_SkechersWeeks[Skechers Week], d_SkechersWeeks[Date], LastSkDay ),

1

)

)

- WEEKNUM ( LastSkDay, 1 ) //-1

VAR AdjustedWeekNum =

YEAR ( [Date] ) & "_W"

& FORMAT ( WEEKNUM ( [Date], 1 ) + WeekNum_Bias, "00" )

RETURN

IF ( [Date] > LastSkDay, AdjustedWeekNum, SkWeek )

I have tried dividing the sales(D)/sales(W) but doesn't show me by day.

Any suggestion?

Thanks

[amitchandak]

@alexcatala , Not very clear. In you date table you can define week and week start date

Add column week rank

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)

Have meausre like
This Week = CALCULATE(sum('order'[amount]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[amount]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

daily sales is

sum('order'[amount])

or

This Day = CALCULATE(sum('order'[amount]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))

percent =divide([This Day],[This Week])

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[alexcatala]

@amitchandak it defines from 1-7 meaning Monday to Sunday.

Should I then use this measure?

As I have Daily sales and Weekly sales already established and the date(day of the week).

[alexcatala]

@amitchandak 

What I am trying to obtain is the percentage of weekly sales by day. For example: Monday 10%, Tuesday 10%, Wednesday 10%, Thursday 10%,Friday 20%,Saturday 30%,Sunday 10%.

I have the formulas for the daily sales and the weekly sales, date(day of the week, from Monday to Sunday)

I have been trying this:

percentage of total = var totalAmount =CALCULATE(sumx(f_DailySales,[Sales (D)]),d_DateTable[Day_Week],FILTER(all(d_StoresAuto[Store],d_StoresAuto[District])))

return

divide(totalAmount,calculate(sumx(f_DailySales,[Sales (W)]),all(d_DateTable[Day_Week])))

But no success, as I want it to filter it by store, district, year, month if possible.

Thanks

[v-lionel-msft]

Hi @alexcatala ,

Please show us the sample data and expected output  result.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[alexcatala · Accepted solution]

@v-lionel-msft 

Hi, I found a solution.

I have used the field, show values as a percentage of grand total!

Thanks for your time!