Incorrect Grand Total In Power Pivot

Guppi — Thu, 11 Apr 2024 00:55:48 GMT

Hi, guys. I'm having difficulties about this grand totals. I have a pivot table like this down below.

DC/Unit	Customer Group	Actual Maret 2024	Price1
ARM	ABC	5.564.000,00	200.000,00
	CV. MAS AGUNG	2.670.000,00
	CV. SANTOSA JAYA	15.590.000,00	900.000,00
	CV. TRI JAYA	4.920.000,00
	HANAN FITRIA	6.064.000,00	200.000,00
	INDRA HARYO	22.300.000,00	1,00
	MAS AGUNG SUKUN	768.000,00
	ORBIT JAYA	9.000.000,00	200.000,00
	PELANGI MEUBEL - DAMPIT	1.500.000,00
	PT. PURWA ASRI LESTARI	937.000,00
	RAMA MOJOKERTO	760.000,00
	RAMAH JAYA	26.380.000,00	1,00
	SUMBER TEKNIK	6.000.000,00	200.000,00
	TK. FARIDA JAYA PLASTIK	13.100.000,00	500.000,00
	TK. HOO	6.624.000,00	200.000,00
	TK. MEGA JAYA ELEKTRONIK	5.700.000,00	200.000,00
	TK. MORO SENENG	4.900.000,00
	TK. PLASTIK LANCAR JAYA	7.112.000,00	200.000,00
	TK. SUMBER JAYA 2 - MAGERSARI	12.679.000,00	500.000,00
	UNTUNG BERSAMA	11.160.000,00	500.000,00
	WINNERS	5.802.000,00	200.000,00
ARM	Total	169.530.000,00	12,00

This pivot table is created using 4 tables: DIMDATE, SALES, HADIAH, and STORE.

The DIMDATE[DATE] column has one to many relationship with SALES[DATE] column,
STORE[CODE] column has one to many relationship with SALES[CUSTOMER CODE] column,

HADIAH[TARGET] column also has one to many relationship with STORE[TARGET] column

The Actual Maret 2024 column is created using a simple measure like this:

CALCULATE([SUM OF NETTO]; DIMDATE[MONTH NAME] = "MAR")

So far, this column grand total is correct.

Then I created a Price1 column using this measure:

VAR ACTUAL = [Actual Maret 2024]

VAR AMACTH =

LOOKUPVALUE(

HADIAH[TARGET];

HADIAH[TARGET]; CALCULATE(MAX(HADIAH[TARGET]); HADIAH[TARGET] <= ACTUAL))

VAR APRICE =

LOOKUPVALUE(

HADIAH[HADIAH];

HADIAH[TARGET];

AMATCH)

RETURN

APRICE

So basicly this measure calculate if the HADIAH[TARGET] column is less than ACTUAL MARET 2024 then return the coresponding HADIAH[HADIAH] column.

The HADIAH table is like this:

Target	Hadiah
5.000.000,00	200.000,00
10.000.000,00	500.000,00
15.000.000,00	900.000,00
20.000.000,00	1,00
35.000.000,00	2,00
50.000.000,00	3,50
75.000.000,00	5,50
150.000.000,00	12,00
250.000.000,00	22,00

I also have tried this measure:

VAR ACTUAL = [Actual Maret 2024]

VAR AMACTH =

LOOKUPVALUE(

HADIAH[TARGET];

HADIAH[TARGET]; CALCULATE(MAX(HADIAH[TARGET]); HADIAH[TARGET] <= ACTUAL))

VAR APRICE =

LOOKUPVALUE(

HADIAH[HADIAH];

HADIAH[TARGET];

AMATCH)

RETURN
SUMX(
SUMMARIZE(

STORE;

STORE[DC/UNIT];

STORE[CUSTOMER GROUP];

"TOTAL";

APRICE);

[TOTAL]

)

but the grand total is also incorrect, too much incorrect.

The Price1 column grand total should be = 4.000.002

Where am I doing wrong?

I hope I provide enough data, so you guys can help me out. Many thanks

Re: Incorrect Grand Total In Power Pivot

Anonymous — Fri, 12 Apr 2024 01:18:21 GMT

HI Guppi,

It seems like a common measure total issue when you use measure expression to calculate with multiple aggregations, you can take a look at the Greg’s blog to know how to handle this scenario:

Measure Totals, The Final Word

Regards,

Xiaoxin Sheng

topic Re: Incorrect Grand Total In Power Pivot in DAX Commands and Tips

Incorrect Grand Total In Power Pivot

Re: Incorrect Grand Total In Power Pivot