Forum Discussion
Helper IDAX cumulate SUM issue
Hi all,
I have a problem with this DAX code: my goal is to produce a cumulate sum starting from a fact table (FORNITORI_F).
1) SommaImporti: I group by FK_ID_ANAGFORNITOREAGGREGATO, sum Import and Rank it foreach ANAGFORNITOREAGGREGATO (step OK)
2) SommaTotale: SUM overall (i.e. denominator for % at the end) (step OK)
3) TabellaOrdinata: ISSUE - (step KO): here I have to produce the cumulate sum row by row, but as you can see in the picture, for all rows the column SommaCumulata is the same (and it's equal to var SommaTotale of step 2)
Could you help me to find the issue and suggest me a fix for it?
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
5 Replies
MattiaMaetiniI try with this other version:
VAR TabellaOrdinata =
ADDCOLUMNS(
SommaImporti,
"SommaCumulata", CALCULATE(SUMX(SommaImporti, [ImportoTotale]), FILTER(SommaImporti, [Range] <= MAXX(SommaImporti, [Range])))
)But the result returned still the same.
BR,
M- MattiaMaetini
Any suggest?
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 _rankTabellaOrdinata = 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- MattiaMaetini
Hi 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,
MHi 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