Forum Discussion
Create a progressive SUM that is dependent from another column
Hi all,
My problem is:
I created a Table with the look of Power BI.
In it, I put a column called 'Label' and a measure called 'Value' which gives me the total value.
The 'Label' data and the values obtained by the 'Amount' measure come from different tables.
What I want:
Create a measure that gives me, per line, the sum of the previous lines, as shown in this table.
When the value of the table or ranking is the smallest, it displays BLANK()
| Label | Rank | Amount | Progressive SUM |
| United Stated | 1 | 50000 | 193000 |
| Austalia | 2 | 40000 | 143000 |
| UK | 3 | 38000 | 103000 |
| Germany | 4 | 35000 | 65000 |
| France | 5 | 30000 | Blank() |
Thanks for your help.
Anonymous try this
Progressive sum = VAR _MaxRank = CALCULATE ( MAX ( Rank1[Rank] ), ALL () ) /*VAR _calc = CALCULATE ( MIN ( Rank1[Amount] ), FILTER ( ALL ( Rank1 ), Rank1[Rank] = _MaxRank ) )*/ VAR _getRank = MAX ( Rank1[Rank] ) VAR _rt = CALCULATE ( SUM ( Rank1[Amount] ), FILTER ( ALL ( Rank1 ), Rank1[Rank] >= _getRank ) ) RETURN IF ( MAX ( Rank1[rank] ) = _MaxRank, "Blank()", _rt )
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
2 Replies
- Nathaniel_C
Community Champion
Anonymous try this
Progressive sum = VAR _MaxRank = CALCULATE ( MAX ( Rank1[Rank] ), ALL () ) /*VAR _calc = CALCULATE ( MIN ( Rank1[Amount] ), FILTER ( ALL ( Rank1 ), Rank1[Rank] = _MaxRank ) )*/ VAR _getRank = MAX ( Rank1[Rank] ) VAR _rt = CALCULATE ( SUM ( Rank1[Amount] ), FILTER ( ALL ( Rank1 ), Rank1[Rank] >= _getRank ) ) RETURN IF ( MAX ( Rank1[rank] ) = _MaxRank, "Blank()", _rt )
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel - VahidDM
Super User
Hi Anonymous
Try this:
Measure = Var _M = CALCULATE(max('Table'[Rank]),all('Table')) Var _A = CALCULATE(sum('Table'[Amount]),filter(all('Table'),'Table'[Rank]>=max('Table'[Rank]))) return if(MAX('Table'[Rank])=_M,BLANK(),_A)Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
