Need to create a weighted measurement

[BlackCat]

I have to create a weighted measurement for a Standard report and now I must create a weighted measurement for the monthly revenue. The calculation used for the Excel report was 

return (Math.Round((TotalRev / NumOfMonths) * (Probability / 100), 2));

I understand that the opportunity status will define the probability that it will close on , so I have to create a logic to split them up, but my calculations are not appearing correct.

[v-stephen-msft]

Hi @BlackCat ,

Sorry, not very clear.

Please provide some sample data and the expected result.

Do you want to calculate the revenue divided by the number of months after the slicer filters the date?

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BlackCat]

Hi Stephen,

I have sent a screenshot of what I am trying to accomplish. Basically I have a report already configured with the following headers: Divisions, Proposal number, Full Name, Oppurtunity status, Deal Stage %, Opportunity Title, Client, Region and you can see the other headers. I've highlighted the calculations I have to perform to add those fields to the report. I am having difficulty as I am new to this type of reporting in BI. Any help is much appreciated.

[BlackCat]

[BlackCat]

[BlackCat]

To Calculate Standard and Weight monthly revenues we used the below code in excel

Standard:

            return (Math.Round((TotalRev / NumOfMonths), 2));

Weighted:

            return (Math.Round((TotalRev / NumOfMonths) * (Probability / 100), 2));

So total revenue you would know from the deal and the number of Months you would calculate base on the number of months between the deal contract starts and ends.

For my standard calculation, I used this measurement in Power BI

Revenue =

ROUND(

CALCULATE(

SUMX(Deal, [Contract Value per Month] * [Selected Months] /*(DATEDIFF(MAX(MIN('Revenue Calendar'[Date]),[Contract Start]), MIN(MAX('Revenue Calendar'[Date]),[Contract End]), MONTH) + 1) */ )

, FILTER(Deal

, Deal[Contract Start] <= MAX('Revenue Calendar'[Date]) && Deal[Contract End] >= MIN('Revenue Calendar'[Date])

)

)

, 0

)

For the weighted calculations it figures out what % it is by using the “Opp Status” selected for the opportunity. I can send you the report in a private message, I'll replace the real data with fictional.

[Anonymous]

@BlackCat could you please post the calculation you've tried in Power BI? 

[BlackCat]

Hi Sean, this is the calculation I used...but doesn't seem correct. 

Monthly Revenue RT(MY) weighted by Opportunity Status =

VAR __CATEGORY_VALUES = VALUES('DealPipelineStage'[Opportunity Status])

RETURN

    DIVIDE(

        SUMX(

            KEEPFILTERS(__CATEGORY_VALUES),

            CALCULATE(

                [Monthly Revenue RT(MY)]

                    * COUNTA('DealPipelineStage'[Probability])

            )

        ),

        SUMX(

            KEEPFILTERS(__CATEGORY_VALUES),

            CALCULATE(COUNTA('DealPipelineStage'[Opportunity Status]))