Forum Discussion
Solved
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...
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'))
)