Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bsas
Post Patron
Post Patron

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
Ashish_Mathur
Super User
Super User

Hi,

 

Is your year end date 31/12?

 


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

@Ashish_Mathur

 

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

v-caliao-msft
Employee
Employee

@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

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

 

 

@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])))

 

 

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/

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.