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))
Hi kusanagi
Please check your rate measure
3A01----Rate should be 54.69
Create a measure for running total
running 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
)
)
)
RETURN
r_t
Create 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.