Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Correct Subtotal with a twist

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

 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    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

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago
      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's avatar
        Greg_Deckler
        Community Champion
        2 years ago

        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