## Total sales by month

Anything related to PowerPivot and DAX Formuale
yoshi_anko
Posts: 5
Joined: Sun Nov 22, 2020 9:14 pm

### Total sales by month

Hello, I want to make sales mix percent in each month. Transactions data has more than 5 million data, customer number, product number, product category, month, sales, cost of sales, delivery conditions.

Then I would like to make pivot table by product category, sales %, mark up% , sales % should be this month's %, so each month need to be 100% at the bottom.
I made total sales measures as follows
CALCULATE ( SUM ( ByStoreByJAN[Total_Taxable] ), ALL (ByStoreByJAN[SoldMonth]))

This results did not become as 5,300,565.73 each row, instead, looks entire sales total by each category. A group need to be 737,702.39 divide by 5,300,565.73, group B need to be 762,831.87 divide by 5,300,565.73, it means each row needs to be divide by total this month's sales, which in this case is 5,300,565.73.

Appreciate to teach me how to rectify this formula.

Thanks,
Yoshi
Attachments
Excel.png (15.28 KiB) Viewed 247 times

MattAllington
Posts: 1150
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

### Re: Total sales by month

Try
CALCULATE ( SUM ( ByStoreByJAN[Total_Taxable] ), ALL (ByStoreByJAN[product category))
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

yoshi_anko
Posts: 5
Joined: Sun Nov 22, 2020 9:14 pm

### Re: Total sales by month

Thanks Matt for reply. Since I don't have product category on this query I made one additional column on the [ByStorebyJAN] query by using Productcategory

=RELATED(GENRE1[GENREName(English)]), then change the measures to

=CALCULATE ( SUM ( ByStoreByJAN[Total_Taxable] ), ALL (ByStoreByJAN[Productcategory]))
but it did not work. I think my relationship establishment must be wrong?

Thanks,

Yoshi
Attachments
Excel2.png (25.17 KiB) Viewed 230 times
Excel.png (58.71 KiB) Viewed 230 times

MattAllington
Posts: 1150
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

### Re: Total sales by month

It is not possible to write DAX without full knowledge of the model. My assumption was that your original pivot table contained product category (A group, B Group, etc). It seems this is not product category. What every column it is, that is what is needed inside the ALL in my formula.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

yoshi_anko
Posts: 5
Joined: Sun Nov 22, 2020 9:14 pm

### Re: Total sales by month

Matt, thanks for your reply and apologies for making you confused. Let me explain from the beginning, kindly ignore my previous post.

1. One transactions file (5million data), Customer, Item number, Total Quantity, Total extended amount, Total extended cost, Total Taxable sales, Sold Month and other column.
2. More than two look up table, first one is item master, second is GENRE1 table.
Item master [Item_Number]<- ByStoreByJAN[Item Number]
GENRE1[GENRE1],-Item master[GENRE1]

4. On the powerpivot, I made a couple of measure.
Net Sales \$ = SUM(ByStoreByJAN[Total_Taxable])
Total Sales = CALCULATE ( SUM ( ByStoreByJAN[Total_Taxable] ), ALL (ByStoreByJAN[GENRE1]))
Q. I need Total Sales section as month total's number, in this case 5,300,565.73 for each row, How should I set up?

Thanks,

Yoshi
Attachments
Excel3.png (11.24 KiB) Viewed 206 times
Excel2.png (16.74 KiB) Viewed 206 times
Excel.png (35.42 KiB) Viewed 206 times