Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
Hi,
See if this works. You may download my PBI file from here.
Hope this helps.
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
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
Hi,
See if this works. You may download my PBI file from here.
Hope this helps.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
82 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |