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 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
For reference ( This is showing up wrong as its doubling the revenue) :
Kindly help in resolving this.
Regards,
Himanshu
Solved! Go to 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
@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])))
Its giving me an error when I'm trying to create a 2nd calculated column using the above calculation.
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
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 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |