Get sum of calculated measures

[Anonymous]

Hi,

I've been struggling with this problem for weeks now, really need some help to sort it out.

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Get-end-of-the-selected-month-in-a-column/m-p...

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.

Sample data 

Thank you so much for your help!

[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)
)

[Anonymous]

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. 

[Greg_Deckler]

@Anonymous This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
SumScoreMeasure = SUMX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])

etc.

You should be able to combine that with the technique shown here: 

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

[Anonymous]

Thanks for commenting @Greg_Deckler 

I've tried your methods at a very early stage but it didn't work. 

I assume SUMX doesn't work well with a dynamically calculated measure.

Here is the sample data that I'm struggling with in case you want to have a look.

https://drive.google.com/file/d/1iNbfYCKafi4jVbtNrGHhCmhP6aC44Fky/view?usp=sharing

Thank you for your help!

[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.

[Anonymous]

@amitchandak There is a sample data link available in my original post.

Here is the link  https://drive.google.com/file/d/1iNbfYCKafi4jVbtNrGHhCmhP6aC44Fky/view?usp=sharing

Thank you so much for your help. 

[lbendlin]

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))

[Anonymous]

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.

[Anonymous]

Sorry @lbendlin  my bad, it's been fixed now.

I was trying different things before and forgot to change it back. 

Still, this doesn't change the way it behaves in other measures though.