Forum Discussion
Accumulated data without date
Dear all experts,
I am currently transforming an excel file to PBI, but I cannot find a correct formula for column D.
Column B is a measure: qty = count('table'[ID])
Column C is also using [qty], but show value as percent
The order of the row is decending [qty]
Coulmn D should be the cumulated data of all the rows in the front, and it will add up to 100% in the last row.
In Excel: D4=D3+C4; D5=D4+C5; D6=D5+C6
I try to look up a solution, but all the solutions I found are based on the date, so those are not for me.
Thanks for your support
Hi kusanagi
Please check your rate measure
3A01----Rate should be 54.69
Create a measure for running totalrunning total =VAR _rnk = RANKX(ALLSELECTED('Table'[Item]),[qty_m],,DESC,Dense)VAR r_t =CALCULATE([qty_m],FILTER(ALLSELECTED('Table'[Item]),_rnk >= RANKX(ALLSELECTED('Table'[Item]),[qty_m],,DESC,DENSE)))
RETURNr_tCreate measure for total (overall)total_m =CALCULATE(SUM('Table'[Qty]),ALLSELECTED('Table'))
last measure to get your result
cum_% =DIVIDE( [running total], [total_m], 0)
If this answers your questions, kindly accept it as a solution and give kudos.Hi kusanagi ,
This is a good use case for the visual calculations that will allow you to have a cumulative calculation over the rate values.
https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-visual-calculations-overview
In this case you need to use a runningsum over the percentage the syntax will be similar to this_:
Running sum = RUNNINGSUM([%GT qty], ORDERBY([qty], DESC))
5 Replies
Hi kusanagi
Please check your rate measure
3A01----Rate should be 54.69
Create a measure for running totalrunning total =VAR _rnk = RANKX(ALLSELECTED('Table'[Item]),[qty_m],,DESC,Dense)VAR r_t =CALCULATE([qty_m],FILTER(ALLSELECTED('Table'[Item]),_rnk >= RANKX(ALLSELECTED('Table'[Item]),[qty_m],,DESC,DENSE)))
RETURNr_tCreate measure for total (overall)total_m =CALCULATE(SUM('Table'[Qty]),ALLSELECTED('Table'))
last measure to get your result
cum_% =DIVIDE( [running total], [total_m], 0)
If this answers your questions, kindly accept it as a solution and give kudos.Hi kusanagi ,
This is a good use case for the visual calculations that will allow you to have a cumulative calculation over the rate values.
https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-visual-calculations-overview
In this case you need to use a runningsum over the percentage the syntax will be similar to this_:
Running sum = RUNNINGSUM([%GT qty], ORDERBY([qty], DESC))
use this
cum_pct =
VAR Rank = RANKX(ALL('table'[Item]), [qty], , DESC, DENSE)
RETURN
DIVIDE(
SUMX(
FILTER(ALL('table'), RANKX(ALL('table'[Item]), [qty], , DESC, DENSE) <= Rank),
[qty]
),
CALCULATE([qty], ALL('table'))
)Hi mdaatifraza5556 ,
Thank you for being part of the Microsoft Fabric Community. Special thanks to MFelix for suggesting the use of visual calculations with RUNNINGSUM(), which is a good fit for your scenario with percentage values sorted in descending order and no date dependency.
I appreciate your quick participation in the discussion kusanagi , MFelix .
Best regards,
Yugandhar _CST Team.
