Subtotals and summaries not working

[ebrownretail]

Hi!

I have a table that helps me determine what i should order. The measures work at a row level, but the subtotal/summary is not always working. 

As you can see in the below screenshot, i have a DC, Store, and order listed. The order column is subtotaling, but the subtotal is not right. In Excel it is showing a different amount. 

Regional DC	Store Number	Building City	Building State/Prov	Sum of On Hand	Total On Order	Total Pipeline	Prior Week Sales	Order
								3,185
6006	306	ARAB	AL	70	0	70	43	49
6006	423	ALABASTER	AL	152	0	152	92	147
6006	1469	CHATTANOOGA	TN	56	0	56	35	49
6010	604	DOTHAN	AL	1	49	50	46	98
6010	944	CRESTVIEW	FL	65	0	65	50	98
6010	2630	JESUP	GA	0	98	98	49	49
6020	2081	CLEARWATER	FL	58	0	58	29	49

The other issue i am having is that i cannot then take this large set of data and summarize it by DC. It si not bringing all the DCs through. 

I am using this measure to calculate my order. 

Order =

IF([Curr item Trait] > 0, IF([Order Need] < 0, BLANK(), MROUND([Order Need],49)), BLANK())

These are the other measures used to make the order measure:

Order Need =

[Curr WOS] - [Total Pipeline]

Curr WOS =

 [Prior Week Sales] * CALCULATE(SELECTEDVALUE('WOS Listing'[WOS]),FILTER('WOS Listing', 'WOS Listing'[Month] = [Current_month]))

Prior Week Sales =

CALCULATE(SUMX('All Sales History (Not Curr WK)','All Sales History (Not Curr WK)'[POS Sales]),FILTER('All Sales History (Not Curr WK)',CALCULATE(MAX(Dates[Walmart Week]),FILTER(Dates,Dates[Date] = TODAY()-7))))

Total Pipeline =

[Total On Order] + SUMX('Current Pipeline','Current Pipeline'[On Hand])

Total On Order =

SUMX('Current Pipeline', 'Current Pipeline'[On Order]) + SUMX('Current Pipeline', 'Current Pipeline'[In Warehouse]) + SUMX('Current Pipeline','Current Pipeline'[In Transit])

[sivarajan21 · Accepted solution]

Thanks @amitchandak @Anonymous 

Hi @ebrownretail ,

If I am correct, for your first issue, you have created a measure to calculate your order which is not returning correct subtotal.From the sample data provided as below:

Regional DC	Store Number	Building City	Building State/Prov	Order
6006	306	ARAB	AL	49
6006	423	ALABASTER	AL	147
6006	1469	CHATTANOOGA	TN	49
6010	604	DOTHAN	AL	98
6010	944	CRESTVIEW	FL	98
6010	2630	JESUP	GA	49
6020	2081	CLEARWATER	FL	49

You can use a measure as below to calculate correct subtotal for your order measure:

Order sum = CALCULATE(SUM(Merchandise[Order]),ALLEXCEPT(Merchandise,Merchandise[Regional DC]))

Result is:

If this is not correct,I suggest you to provide a sample data as @Anonymous  said and we will be able to assist you with this.

Thanks in advance!

[amitchandak]

@ebrownretail , In such case you can use measures like

Curr WOS new= sumx(Values(Table[Regional DC]), [Curr WOS])

Order  new= sumx(Values(Table[Regional DC]), [Order ])

[ebrownretail]

Hi!

That did not work. Below is what the values should have been:

Row Labels	Sum of Order
6006	245
6010	245
6020	147
6023	147
6024	49
6026	49
6027	196
6030	49
6035	147
6036	49
6037	98
6039	98
6040	49
6048	245
6054	98
6068	147
6070	588
7026	49
7034	245
7035	147
7036	98
Grand Total	3185

But the formula came back with this:

[Anonymous]

Hi @ebrownretail,

According your statement, I think there should at least four tables in your data model: 'Current Pipeline’, 'All Sales History','WOS Listing' and 'Date'.

It seems that [Order] measure couldn't return correct data. Or we just need to create a new measure to sum [Order] based on [Regional DC] as amitchandak mentioned.

So I suggest you to share a easier sample file with a same data model with us. This will make us easier to find the solution.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.