Correct Subtotal with a twist

[Anonymous]

Hi,

I am trying to show project status by quarter and have to fill in quarters where there is no change in activity.

I want the flow distribution column to be 3 (the total for each quarter) 

but using the has one value function in measure 3, I get 4.  

Flow distribution is defined as being if the project is active or not in that quarter and I had to fill this in for Q2.

Can someone please help as this is usually so easy so I am wondering why it doesn't work.  Thanks

[Greg_Deckler]

@Anonymous What is your measure formula?

[Anonymous]

Total = if(HASONEVALUE('vwFiscalCalendar'[Fiscal Year Quarter]),[Feature Epic Story Point Count],sumx('Flow Distribution','Flow Distribution'Feature Epic Story Point Count]))

Here is the data. I am trying to get a count of 3 (1 for each row).  It's a bit involved as to how this is dervied because of the data in the gaps and every IssueKey needs a record for every quarter if not done.  

Status Text = min('Flow Distribution'[Final Status])

STATUTS TEXT DATE = CALCULATE([Status text],CALCULATETABLE(LASTNONBLANK('vwFiscalCalendar'[CalendarDate],CALCULATE([Status text])),DATESBETWEEN('vwFiscalCalendar'[CalendarDate],MINX(ALL('vwFiscalCalendar'),'vwFiscalCalendar'[CalendarDate]),Max('vwFiscalCalendar'[CalendarDate]))))

MAX DONE DATE = calculate(max('Flow Distribution'[CreatedOnNext]),'Flow Distribution'[Final Status]="Done")

Distribution = if(AND([STATUTS TEXT DATE]="Done",[Done status achieved on]=BLANK()),BLANK(),[STATUTS TEXT DATE])

THanks

[Greg_Deckler · Accepted solution]

@Anonymous So, is this the total for the Flow Distribution? And the Flow Distribution is your Feature Epic Story Point Count measure? If this is the case, then it should be something like this:

Flow Distribution Total = 
  VAR __Table = SUMMARIZE( 'vwFiscalCalendar', [Fiscal Year Quarter], "__Value", [Feature Epic Story Point Count] )
  VAR __Result = IF( HASONEVALUE( 'vwFiscalCalendar'[Fiscal Year Quarter]), [Feature Epic Story Point Count], SUMX( __Table, [__Value] ) )
RETURN
  __Result

[Anonymous]

@Greg_Deckler 

Please see above. Thanks