Forum Discussion
Dynamic Percent total based on date and store
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 |
3 Replies
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 LinkedinHi 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
