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.
I'm trying to calculate a running total but I can't get it for some reason. Here's what my setup is:
So as you can see it's not adding the previous months value to the next. Here's what my measures involved look like:
Any ideas?
Solved! Go to Solution.
Hi,
According to your description, I create four tables to test:
Please take following steps:
1)Create four measures:
Annual Producer Sum = CALCULATE(SUM(User[Annual_Producer_Goal_c]))
Sum of Quota % max per Month = MAXX(KEEPFILTERS(VALUES('Quota Percentage'[Month])),CALCULATE(SUM('Quota Percentage'[Quota %])))
* Quoted Revenue = CALCULATE([Annual Producer Sum]*[Sum of Quota % max per Month])
* Cumulative Quoted Revenue =
VAR maxd =
MAX ( 'Date'[Date] )
RETURN
SUMX (
SUMMARIZE (
FILTER ( ALLSELECTED ( 'Opportunity' ), 'Opportunity'[Value] <= maxd ),
'Opportunity'[Month & Year],
"Revenue", [* Quoted Revenue]
),
[Revenue]
)
2)The result shows:
Here is my test pbix file:
Best Regards,
Giotto Zhi
Hi,
According to your description, I create four tables to test:
Please take following steps:
1)Create four measures:
Annual Producer Sum = CALCULATE(SUM(User[Annual_Producer_Goal_c]))
Sum of Quota % max per Month = MAXX(KEEPFILTERS(VALUES('Quota Percentage'[Month])),CALCULATE(SUM('Quota Percentage'[Quota %])))
* Quoted Revenue = CALCULATE([Annual Producer Sum]*[Sum of Quota % max per Month])
* Cumulative Quoted Revenue =
VAR maxd =
MAX ( 'Date'[Date] )
RETURN
SUMX (
SUMMARIZE (
FILTER ( ALLSELECTED ( 'Opportunity' ), 'Opportunity'[Value] <= maxd ),
'Opportunity'[Month & Year],
"Revenue", [* Quoted Revenue]
),
[Revenue]
)
2)The result shows:
Here is my test pbix file:
Best Regards,
Giotto Zhi
So the filter in your cummulative measure looks wrong as it is comparing Opportunity[Value] to MAX(Date[Date])
The normal pattern would be:
* Cumulative Quote Revenue = CALCULATE([* Quoted Revenue], FILTER( ALL(Date[Date]), 'Date'[Date] <= MAX('Date'[Date]) ) )
But I don't think that will produce the correct result because it's effectively going to do SUM( Annual_Producer_Goal_c ) * MAX( [Quota %]) whereas I believe that you probably want to do SUM( Annual_producer_Goal__c * [Quota &] )
The following expression will do the multiplication on a day by day basis then add the result.
* Cumulative Quote Revenue = SUMX( FILTER( ALL(Date[Date]), 'Date'[Date] <= MAX('Date'[Date]) ) ), [* Quoted Revenue] )
The formula seems correct unless there is issue with Opportunity value column.
If possible please share a sample pbix file after removing sensitive information.Thanks.
My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
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 |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |