Forum Discussion

Arranafc19's avatar
Arranafc19
Icon for Helper IV rankHelper IV
7 years ago

Cumulative Percentage to skip null row

I am working on calculating the cumulative percentage for a set of data , however I only want the percentage to start totalling after the first row.

 

At present , I am getting the below:

 

The current measure I am using is 

 

 
Cumulative Total =
CALCULATE (
          COUNT ( 'Actual v Expected'[Count] ),
    FILTER (
        ALLSELECTED ( 'Actual v Expected' ),
        ('Actual v Expected'[MONTH_OF_CLOSURE] <= MAX ( 'Actual v Expected'[MONTH_OF_CLOSURE] ))
    ))
 
 
This is calculating the cumulative percentage correct but it is including my null month which I want to exclude.I need the measure to only start a running total on the rows where the month of closure is not null.
 
See below the expected outcome:
 
 
As you can see , i need only from 0.4 down on the percentage close to total up , and I cant exclude the top row as I need them in the percetnage calculation. Can anyone advise how to fix this ?
Need Help
Show and Tell

5 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    HI, Arranafc19 

    Just try this formula to create the measure

    Cumulative Percentage = DIVIDE(CALCULATE([Number of Closed],FILTER(ALLSELECTED('Actual v Expected'),'Actual v Expected'[MONTH_OF_CLOSURE]<>BLANK()&&'Actual v Expected'[MONTH_OF_CLOSURE]<=MAX('Actual v Expected'[MONTH_OF_CLOSURE]))),CALCULATE(SUM('Actual v Expected'[Count]),ALLSELECTED('Actual v Expected')))

    Result:

     

    Best Regards,

    Lin

    • Arranafc19's avatar
      Arranafc19
      Icon for Helper IV rankHelper IV
      7 years ago

      hi v-lili6-msft 

       

      Thank you for your help.

       

      It is throwing me an error on this part as it is expecting a measure but my count is created when I when I gropued the dataset by date and month of closure , a count was created then.

      The number of closed figure is thenshown as I summarise the count field in my table.

       

      So for example , my dataset is calculating a count per start date and month of closure eg :

       

      start date          month of closure    count

      11-04-2019                null                       1

      11-04-2019                 1                          20

      11-04-2019                 2                         35

       

       

      then on my report level i am filtering by start date so I am getting a total number of closed where the start date is between the chosen dates.

       

      Is it an easy change to suit your measure ?

      • v-lili6-msft's avatar
        v-lili6-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        hi, Arranafc19 

        what is "Number of Closed" in your report? a measure or a column?

        Try this formula

        Cumulative Percentage = DIVIDE(CALCULATE( COUNT ( 'Actual v Expected'[Count] ),FILTER(ALLSELECTED('Actual v Expected'),'Actual v Expected'[MONTH_OF_CLOSURE]<>BLANK()&&'Actual v Expected'[MONTH_OF_CLOSURE]<=MAX('Actual v Expected'[MONTH_OF_CLOSURE]))),CALCULATE(SUM('Actual v Expected'[Count]),ALLSELECTED('Actual v Expected')))

        Best Regards,

        Lin