Forum Discussion
Solved
Running total/Cumulative sum
I have created a measure 1 and I want to create a running total measure depending upon the measure 1, I have been through many blogs and links on the internet but not able to get the desired output ...
Nimai123 , Try like
Running Open Stragglers Low Risk = Var a = MINX(ALLSELECTED(Qtr),Qtr[Week Num]) Return CALCULATE(Sum(bookings_reporting_view[line_item_acv_price_converted]), FILTER(ALLSELECTED(bookings_reporting_view), bookings_reporting_view[med_fq_Qtr Name] < MIN(Qtr[Qtr Name])&& bookings_reporting_view[cld_weekhelp] <= MIN(Qtr[Week Num])&& bookings_reporting_view[cld_weekhelp] >= a && OR(bookings_reporting_view[stage] in {"1 - Discover Opportunity","1 - Renewal Opportunity","2 - Renewal Initiated","3 - Renewal Low Risk","2 - Qualify Opportunity","3 - Validate Solution"}, bookings_reporting_view[stage] in {"4 - Renewal Procurement","5 - Manage Procurement"}) && bookings_reporting_view[isclosed] = {"0"}))Nimai123 ,
Try below measure to get your expected result:
Running Stragglers Low Risk=Var Res = CALCULATE(sum(bookings_reporting_view[line_item_acv_price_converted]),FILTER(ALLSELECTED(bookings_reporting_view),bookings_reporting_view[med_fq_Qtr Name] < MIN(Qtr[Qtr Name]) &&bookings_reporting_view[cld_weekhelp] <=MIN(Qtr[Week Num]) &&bookings_reporting_view[cld_weekhelp] >= (CALCULATE(MIN(Qtr[Week Num]),FILTER(ALLSELECTED(Qtr),Qtr[Week Num]<=MIN(Qtr[Week Num]))))&& bookings_reporting_view[stage] in {"1 - Discover Opportunity","1 - Renewal Opportunity","2 - Renewal Initiated","3 - Renewal Low Risk","2 - Qualify Opportunity","3 - Validate Solution" } &&bookings_reporting_view[isclosed] = {"0"}))RETURN Res
Nimai123 - Sample source data would help. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
But, the general pattern is:
Cumulative Measure =
VAR __Current = [Weeknum]
VAR __Table =
SUMMARIZE(
FILTER('Table',[Weeknum] <= __Current),
[Weeknum],
"Measure",[Measure]
)
RETURN
SUMX(__Table,[Measure])
Thanks, Greg
The data is confidential so I will be sending you the sample data file on Personal chat, hope it works.
The result required when selecting FY21-Q1 in the slicer
