DAX total running on a temp aggregate var table issue

[MattiaMaetini]

Hi all,

My goal it's to obtain a running total from my ranked aggregate var table.

This could be a set of valid data input (FORNITORI_F into the dax code below):

ID Fornitore	FK_ID Fornitore Aggregato	Importo
5	10	100
6	10	200
1	3	150
2	2	1000
4	2	2000
8	2	3000

As a first temp output I'm able to obtain this var table:

DEFINE

VAR SommaImporti =
ADDCOLUMNS(
SUMMARIZE(
FILTER(FORNITORI_F, FORNITORI_F[Anno] = 2024 && FORNITORI_F[FK_ID_CALENDARIO]=1004),
FORNITORI_F[FK_ID_ANAGFORNITOREAGGREGATO],
"ImportoTotale", SUM(FORNITORI_F[ImportoPosizioneIVAInclusa])
),
"Range", RANKX(
SUMMARIZE(
FILTER(FORNITORI_F, FORNITORI_F[Anno] = 2024 && FORNITORI_F[FK_ID_CALENDARIO]=1004),
FORNITORI_F[FK_ID_ANAGFORNITOREAGGREGATO],
"ImportoTotale", SUM(FORNITORI_F[ImportoPosizioneIVAInclusa])
),
[ImportoTotale], , DESC
)
)

FK_ID AnagFornitoreAggregato (group by)	Importo (sum)	Ranking
2	6000	1
10	300	2
3	150	3

Var SommaTotale will be 6450:

VAR SommaTotale = SUMX(SommaImporti, [ImportoTotale])

Now I need to creata a running total like this:

FK_ID AnagFornitoreAggregato	Importo	Ranking	RUNNING TOTAL
2	6000	1	6000
10	300	2	6300
3	150	3	6450

But what I have is:

FK_ID AnagFornitoreAggregato	Importo	Ranking	RUNNING TOTAL
2	6000	1	6450
10	300	2	6450
3	150	3	6450

VAR TabellaOrdinata =
ADDCOLUMNS(
SommaImporti,
"SommaCumulata", CALCULATE(SUMX(SommaImporti, [ImportoTotale]),

FILTER(SommaImporti, [Range] < MAXX(SommaImporti, [Range])))
)

I'm not able to iterate over my temp table SommaImporti.

Could you help me to solve it?

Tks in advance,

BR,

M

[rajendraongole1]

Hi @MattiaMaetini - In your TabellaOrdinata variable, calculate the cumulative total by filtering SommaImporti to include only those rows where the rank is less than or equal to the current row's rank.

Updated formula FYR:

DEFINE
VAR SommaImporti =
ADDCOLUMNS(
SUMMARIZE(
FILTER(FORNITORI_F, FORNITORI_F[Anno] = 2024 && FORNITORI_F[FK_ID_CALENDARIO] = 1004),
FORNITORI_F[FK_ID_ANAGFORNITOREAGGREGATO],
"ImportoTotale", SUM(FORNITORI_F[ImportoPosizioneIVAInclusa])
),
"Range", RANKX(
SUMMARIZE(
FILTER(FORNITORI_F, FORNITORI_F[Anno] = 2024 && FORNITORI_F[FK_ID_CALENDARIO] = 1004),
FORNITORI_F[FK_ID_ANAGFORNITOREAGGREGATO],
"ImportoTotale", SUM(FORNITORI_F[ImportoPosizioneIVAInclusa])
),
[ImportoTotale], , DESC
)
)

VAR SommaTotale = SUMX(SommaImporti, [ImportoTotale])

VAR TabellaOrdinata =
ADDCOLUMNS(
SommaImporti,
"SommaCumulata",
CALCULATE(
SUMX(
FILTER(SommaImporti, [Range] <= EARLIER([Range])),
[ImportoTotale]
)
)
)

RETURN
TabellaOrdinata

changes , The EARLIER function allows us to reference the current row’s rank (Range) within the filter of SommaImporti, so that the filter accumulates totals up to the current rank.

Hope it works. 

[MattiaMaetini · Accepted solution]

Hi @rajendraongole1 ,

Unfortunately code doesn't work: please find dax studio screenshot on the bottom.

It seems try to filter a column from a VAR table create some problem.

Any other suggest in order to solve it?

BR,
M