Double revenue showing up in Card

[Anonymous]

Hi All,

I have a situation in my report where a single deal is having 2 stakeholders but when I'm calculating total revenue it is adding both the rows whereas it should add only distinct deal.

Like in the below screenshot I want to show the sum as $341.10 but it is doublng since I have 2 stakeholders on the same deal.

How can this be resolved?

I want to calculated Revenue with distinct Deal ID like below

Gross Revenue = CALCULATE(DISTINCTCOUNT(View_DealClosing[ID]) && CALCULATE(SUM(View_DealClosing[Estrevenue])))

For reference ( This is showing up wrong as its doubling the revenue) :

Kindly help in resolving this.

Regards,

Himanshu

[Anonymous]

@amitchandak can you help here?

Regards,

Himanshu

[amitchandak]

@Anonymous , Try like

Gross Revenue = sumx(values(View_DealClosing[ID]), CALCULATE(SUM(View_DealClosing[Estrevenue])))

but what is the benefit if both are sum

may be

Gross Revenue = sumx(values(View_DealClosing[ID]), CALCULATE(max(View_DealClosing[Estrevenue])))

[Anonymous]

Its giving me an error when I'm trying to create a 2nd calculated column using the above calculation.

[v-easonf-msft · Accepted solution]

Hi,  @Anonymous 

Since this result is being calculated multiple times, you can try to divide the result by each count of id (2).

C_Gross Revenue = 
VAR count_eachID =
    CALCULATE (
        COUNTROWS ( View_DealClosing ),
        ALLEXCEPT ( View_DealClosing, View_DealClosing[ID] )
    )
RETURN
    View_DealClosing[Estrevenue] / count_eachID

Youcan also try measure to change total value in table visual as below:

Gross Revenue =
IF (
    HASONEVALUE ( View_DealClosing[ID] ),
    SUM ( View_DealClosing[Estrevenue] ),
    SUM ( View_DealClosing[Estrevenue] ) / 2
)

Best Regards,
Community Support Team _ Eason