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
