Forum Discussion

brokencornets's avatar
brokencornets
Helper IV
4 years ago
Solved

Not totalling Measure field

Hi all

 

My original query here was resolved: Solved: Filtering using date table not working how I would... - Microsoft Power BI Community

 

But following on from that, now I have a working measure at a line item level, I don't see to be able to sum this measure correctly?

 

I get why, because the calculation is counting the total number of days between a start/end date (365 in this case) and then dividing by the frequency x, so at a total level it'll still be calculating 365/(some form of x) - but how do I get around this and make the total sum up correctly?

 

Thanks in advance,

Phil

 

Measure:

Expected Iterations =
VAR maxdate= max('Date'[Date])
VAR mindate=max(min('Date'[Date]),min('Task List'[First Instance]))
RETURN
if(mindate>maxdate,0,
CALCULATE(datediff(mindate,maxdate,DAY),USERELATIONSHIP('Date'[Date],'Task List'[First Instance]))/sum('Task List'[Freq Days]))
 
Results:

 

Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    4 years ago

    brokencornets 

    You will need to use SUMX to get the correct total.  It is probably easier to use your existing measure in a new one to handle all the context transition.  It will be along these lines.

     

    Expected Iterations Correct Total = SUMX ( 'Task List', [Expected Iterations] )

     

     

    Or, if a single task can be in the table multiple times it will be like this.

     

    Expected Iterations Correct Total = SUMX ( VALUES('Task List'[Task ID]), [Expected Iterations] )

     

     

2 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    4 years ago

    brokencornets 

    You will need to use SUMX to get the correct total.  It is probably easier to use your existing measure in a new one to handle all the context transition.  It will be along these lines.

     

    Expected Iterations Correct Total = SUMX ( 'Task List', [Expected Iterations] )

     

     

    Or, if a single task can be in the table multiple times it will be like this.

     

    Expected Iterations Correct Total = SUMX ( VALUES('Task List'[Task ID]), [Expected Iterations] )

     

     

    • brokencornets's avatar
      brokencornets
      Helper IV
      4 years ago

      Thanks jdbuchanan71 - this works as a sum in the table but I can't use that new measure in a seperate card or it shows as 'Infinity' - any idea why that would be?

       

      EDIT - Ignore this, I had a filter on the table visualisation but not on the card!

       

      Thanks again for your help. Such a simple solution and yet it makes my head hurt.