cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bsas Member
Member

replace 4 measures for cumulative revenue by 1

Hi All,

 

I have a problem with cration of one measure to replace 4 measures mentioned below. Maybe I'm doing something wrong but I'm just not able to resolve this...

 

I have 4 measures to calculate cumulative revenue based on category. I need somehow replace them by one to have less calculations. 

 

My measures are:

 

 

a.Won_cumulative = CALCULATE(SUM(Acc_Opp[ss_revenue_base]),
FILTER(Filter(ALLSELECTED(Acc_Opp),Acc_Opp[Opp-ty Status]="1.Won"),
Acc_Opp[ss_closedate] <= MAX(Acc_Opp[ss_closedate]))) a.Forecast_cumulative = CALCULATE(SUM(Acc_Opp[ss_revenue_base]),
FILTER(Filter(ALLSELECTED(Acc_Opp),Acc_Opp[Opp-ty Status] = "2.Forecast"),
Acc_Opp[ss_closedate] <= MAX(Acc_Opp[ss_closedate]))) a.Best Case_cumulative = CALCULATE(SUM(Acc_Opp[ss_revenue_base]),
FILTER(Filter(ALLSELECTED(Acc_Opp),Acc_Opp[Opp-ty Status] = "3.Best Case"),
Acc_Opp[ss_closedate] <= MAX(Acc_Opp[ss_closeday]))) a.Stretch_cumulative= CALCULATE(SUM(Acc_Opp[ss_revenue_base]),
FILTER(Filter(ALLSELECTED(Acc_Opp),Acc_Opp[Opp-ty Status] = "4.Stretch 0-50%"),
Acc_Opp[ss_closedate] <= MAX(Acc_Opp[ss_closedate])))

 

 

Data model example:

 

namess_closedatess_revenue_baseOpp-ty Status
opp110/10/2017200001.Won
opp27/23/2017350002.Forecast
opp35/19/20177000003.Best Case
opp412/3/2017100001.Won
opp512/3/2017100004.Stretch
opp65/19/20177000001.Won
opp77/23/2017350001.Won
opp810/10/2017200002.Forecast

 

 

Can somebody help me with this? 

8 REPLIES 8
Moderator v-caliao-msft
Moderator

Re: replace 4 measures for cumulative revenue by 1

@bsas,

 

You could ceate another table by using enter table.

Capture.PNG

And then add this column to a slicer, then you can use the measure below to replace your measures.

Measure =
VAR selecteditem =
    IF (
        HASONEFILTER ( Slicer[Slicer] ),
        LASTNONBLANK ( Slicer[Slicer], 1 ),
        BLANK ()
    )
RETURN
    CALCULATE (
        SUM ( Acc_Opp[ss_revenue_base] ),
        FILTER (
            FILTER ( ALLSELECTED ( Acc_Opp ), Acc_Opp[Opp-ty Status] = selecteditem ),
            Acc_Opp[ss_closedate] <= MAX ( Acc_Opp[ss_closedate] )
        )
    )

Regards,

Charlie Liao

Super User
Super User

Re: replace 4 measures for cumulative revenue by 1

Hi,

 

Is your year end date 31/12?

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
bsas Member
Member

Re: replace 4 measures for cumulative revenue by 1

@Ashish_Mathur

 

Yes it is 12/31/2017 for 2017. Starting from 2018 I'll use data only for 2018.

bsas Member
Member

Re: replace 4 measures for cumulative revenue by 1

Hi @v-caliao-msft,

 

Thanks, but slicer doesn't suit my requirements. I need to have one measure because I will use it in other measure, which is linked to slicer "type".

 

measure = IF(ISFILTERED('revenue model'[type]) && 
DISTINCTCOUNT('revenue model'[type])=1,
[Revenue_Cumulative], SUM('Acc_Opp'ss_revenue_base))

I cannot use 4 measures in this logic, so I'm looking for one, which will calculate cumulative revenue for each category.

 

Emaple of chart: (here I use 4 measures one for each cat)

 Untitled.png

 

 

bsas Member
Member

Re: replace 4 measures for cumulative revenue by 1

@Ashish_Mathur @v-caliao-msft,

 

Thanks for your efforts!

 

I finally did it!!! It was so close and oubvious...but for 2 days it fleed from me..

 

 

booking__Cumulative = CALCULATE(SUM(Acc_Opp[ss_revenue_base]), 
FILTER(ALL('Calendar'),'Calendar'[allDates] <=MAX('Calendar'[allDates])))

 

 

Super User
Super User

Re: replace 4 measures for cumulative revenue by 1

Hi @bsas,

 

Your formula will not work correctly in 2018.  When you key in data for January 2018, the cumulative revenue should be only for January 2018 and not from January 2017.  Your formula will accumulate from january 2017.  I believe the correct formula should be:

 

=CALCULATE(SUM(Table1[ss_revenue_base]),DATESYTD('calendar'[Date],"31/12"))

You may download my workbook from here.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
bsas Member
Member

Re: replace 4 measures for cumulative revenue by 1

Hi @Ashish_Mathur,

 

My calendar (calculate table) is build based on CALENDARAUTO( ) which is linked to my data filtered by year.

 

I've tried your variant, it works, but in chart it shows same value for each month as cumulative of all year data, which is not correct for my case.

 

Untitled.png

Super User
Super User

Re: replace 4 measures for cumulative revenue by 1

Hi,

 

Share the link from where i can download your PBIX file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)