Forum Discussion
Get sum of calculated measures
Hi,
I've been struggling with this problem for weeks now, really need some help to sort it out.
I have a dynamically calculated measure [Aged Tickets Days] based on month selection. Then I added another measure [aged_total] to get the total number of tickets that are older than 30 days.
I was able to give 1 value to the rows that are over 30 in Aged tickets days. but as you may know, Power Bi is not very supportive in terms of getting totals of measure.
aged_total =
var aged = IF([Aged Tickets Days] > 30, 1, BLANK())
return IF(HASONEFILTER('All Tickets'[ID]),
aged,
SUMX(FILTER('All Tickets', [Aged Tickets Days] > 30), aged)
)
So I searched to find the solution but haven't been able to resolve it.
https://www.youtube.com/watch?v=Ka7Ds4EAjNQ
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Looks like these tricks only work when calculating column not measure.
Please look at my sample file, advise if I'm doing something wrong.
Thank you so much for your help!
10 Replies
- lbendlin
Super User
Your "Aged Tickets Days" measure seems inconsistent.
Please confirm that is intended.
In general it helps to compute totals by themselves, not involving other measures.
like so:
aged total2 := sumx('All Tickets',if(DATEDIFF('All Tickets'[Created Date],'All Tickets'[Closed Date],DAY)>30,1,0))Thank you lbendlin for confirming it.
Yes, it is intended because I want the measure to return different values based on the ticket status.
e.g.
If a ticket is not closed -> return not_closed
If a ticket is closed && status='closed' -> return closed
If a ticket is closed && status='resolved' -> return resolved
+ Thanks for the suggestion. The problem is I need to check whether tickets are aged at the selected month including not closed ones.
For instance, if I select "May" it should count all tickets that are over 30 days, then
datediff([created date], [closed date], Day)will do the job but I can't get the tickets that are still open because it doesn't have the closed date value yet.
Please let me know if you need more clarification.
- lbendlin
Look again at your Aged Ticket Days measure. It returns closed, ignoring result.
- amitchandak
Anonymous , Try like
aged_total = var aged = IF([Aged Tickets Days] > 30, 1, BLANK()) return IF(HASONEFILTER('All Tickets'[ID]), aged, SUMX(values('All Tickets'[ID]), aged) )Hi amitchandak ,
Thanks for the reply.
I've tried it but it returns all tickets created in the selected month, not the tickets over 30 days.
- amitchandak
Anonymous , I think a formula like this should work.
sumx(Values(Table[ID]) ,if( datediff(max(Created date]), selecteddate(Date[Date]),Day) >30 && isblank(Table[Closed Date]),1,0))
Do you need a bucket on top of it?
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
