cancel
Showing results for
Did you mean:
New Member

## 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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV

## Re: Dynamic Percent total based on date and store

Hi,

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

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
3 REPLIES 3
Super User IV

## Re: Dynamic Percent total based on date and store

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

In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin

New Member

## Re: Dynamic Percent total based on date and store

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

Super User IV

## Re: Dynamic Percent total based on date and store

Hi,

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

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### ‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.