Forum Discussion
Helper IDAX cumulate SUM issue
Hi MattiaMaetini ,
Try this
Total = VAR _FK_ID = SELECTEDVALUE('Table'[FK_ID Fornitore Aggregato]) VAR _table =SUMMARIZE(ALL('Table'),[FK_ID Fornitore Aggregato],"Sum_Importo",[SumImporto]) VAR _table2 = ADDCOLUMNS(_table,"Rank",RANKX(_table,[Sum_Importo],,DESC,Skip)) VAR _Rank = [Ranking] VAR _total = SUMX(FILTER(_table2,[Rank] <=_Rank),[Sum_Importo]) RETURN _totalBest Regards,
Wenbin Zhou
Hi MattiaMaetini ,
Regarding your question, I assumed some data.
Since you are asking for a cumulative value, we need to remove the filter effect of the 'FK_ID_ANAGFORNITOREAGGREGATO' column. In my case, it is the 'ID' column, and I additionally used the 'ALL' function in the second measure.
Rank =
VAR _table1 =
SUMMARIZE ( 'Table', [ID], "ImportToTotal", SUM ( 'Table'[Amount] ) )
VAR _table2 =
ADDCOLUMNS (
_table1,
"Rank",
RANKX (
SUMMARIZE ( ALL ( 'Table' ), [ID], "ImportToTotal", SUM ( 'Table'[Amount] ) ),
[ImportToTotal],
,
DESC
)
)
VAR _rank =
MAXX ( _table2, [Rank] )
RETURN
_rank
TabellaOrdinata =
VAR _a = [Rank]
VAR _table1 =
SUMMARIZE ( ALL ( 'Table' ), [ID], "ImportToTotal", SUM ( 'Table'[Amount] ) )
VAR _table2 =
ADDCOLUMNS (
_table1,
"Rank",
RANKX (
SUMMARIZE ( ALL ( 'Table' ), [ID], "ImportToTotal", SUM ( 'Table'[Amount] ) ),
[ImportToTotal],
,
DESC
)
)
RETURN
SUMX ( FILTER ( _table2, [Rank] <= _a ), [ImportToTotal] )
Final output
You can refer to my expression for modification, if there is still problem, please provide .pbix file without sensitive data or simple data.
If you are unsure how to upload data please refer to
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Best Regards,
Wenbin Zhou
Helper IHi Anonymous ,
tks for your answer.
Unfortunatelly it's not what I need: my goal it's to obtain a running total from my ranked table (SommaImporti).
This could be a set of valid data input:
| 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 output (SommaImporti) I'm able to obtain this var table:
| FK_ID AnagFornitoreAggregato (group by) | Importo (sum) | Ranking |
| 2 | 6000 | 1 |
| 10 | 300 | 2 |
| 3 | 150 | 3 |
Var SommaTotale will be 6450; 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 |
Could you help me to solve it?
Tks in advance,
BR,
M
Hi MattiaMaetini ,
Try this
Total = VAR _FK_ID = SELECTEDVALUE('Table'[FK_ID Fornitore Aggregato]) VAR _table =SUMMARIZE(ALL('Table'),[FK_ID Fornitore Aggregato],"Sum_Importo",[SumImporto]) VAR _table2 = ADDCOLUMNS(_table,"Rank",RANKX(_table,[Sum_Importo],,DESC,Skip)) VAR _Rank = [Ranking] VAR _total = SUMX(FILTER(_table2,[Rank] <=_Rank),[Sum_Importo]) RETURN _totalBest Regards,
Wenbin Zhou