The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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
Solved! Go to 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
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.
Proud to be a Super User! | |
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
User | Count |
---|---|
87 | |
84 | |
36 | |
35 | |
32 |
User | Count |
---|---|
96 | |
75 | |
67 | |
52 | |
52 |