Dynamic Percent total based on date and store

[Anonymous]

Hi I need help with creating measures. 

My data looks like the follows: 

Store	Employee	Date	Sales
A	Joe	10/1/2019	2
A	Ron	10/1/2019	3
A	Amy	10/2/2019	1
A	Joe	10/2/2019	3
B	Tammy	10/1/2019	4
B	James	10/1/2019	2
B	John	10/2/2019	1
B	Smith	10/2/2019	2

I need to get total sales per day for each store and also be able to create a matrix to get the total sales per month for each store, each employee. 

So for example store A my example output would look like this.

Also when grouped based on month I want to see 

for joe in in store A had sales of 0.55 (5/9) for month of october. 

Store	Employee	Date	Sales	Daily Sales
A	Joe	10/1/2019	2	0.4
A	Ron	10/1/2019	3	0.6
A	Amy	10/2/2019	1	0.25
A	Joe	10/2/2019	3	0.75
B	Tammy	10/1/2019	4	0.67
B	James	10/1/2019	2	0.33

[amitchandak]

You can create two columns like

	Total sales=
sumx(filter(table,table[Store] = earlier(table[Store]) && table[Date]=earlier(table[Date])),table[Sales])

	

Daily Sales = divide(table[Sales],table[Total sales]

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Anonymous]

Hi this function does not work when I drill up to the month. For the Month total, I get the same value as the total for the day.  

This is the result I want to get when the data is drilled down to the day

Store	Employee	Date	Sales	Daily Sales Sum	% of Daily Sales
A	Joe	10/1/2019	2	5	40%
A	Ron	10/1/2019	3	5	60%
A	Amy	10/2/2019	1	4	25%
A	Joe	10/2/2019	3	4	75%
B	Tammy	10/1/2019	4	6	67%
B	James	10/1/2019	2	6	33%

THis is the result I want when I drill up to the month

Store	Date	Employee	Monthly Sales	Monthly Sum of Sales	% of Monthly Sum of Sales
A	October	Joe	5	9	55%
B	October	Tammy	4	6	67%

Thanks

[Ashish_Mathur · Accepted solution]

Hi,

See if this works.  You may download my PBI file from here.

Hope this helps.