Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
name | ss_closedate | ss_revenue_base | Opp-ty Status |
opp1 | 10/10/2017 | 20000 | 1.Won |
opp2 | 7/23/2017 | 35000 | 2.Forecast |
opp3 | 5/19/2017 | 700000 | 3.Best Case |
opp4 | 12/3/2017 | 10000 | 1.Won |
opp5 | 12/3/2017 | 10000 | 4.Stretch |
opp6 | 5/19/2017 | 700000 | 1.Won |
opp7 | 7/23/2017 | 35000 | 1.Won |
opp8 | 10/10/2017 | 20000 | 2.Forecast |
Can somebody help me with this?
Hi,
Is your year end date 31/12?
You could ceate another table by using enter table.
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)
@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.
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.
Hi,
Share the link from where i can download your PBIX file.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |