Forum Discussion

Arranafc19's avatar
Arranafc19
Helper IV
7 years ago
Solved

Cumulative Percentage

I am working on a report which calculates the percentage of sold per month , with the null value being the ones not sold. I have managed to create the below dataset and calculated the percetages and ...
Need Help
Show and Tell
  • Cmcmahan's avatar
    Cmcmahan
    7 years ago

    So you're very close.  ALLSELECTED returns all values that are currently being used in the table.  You just need to add an extra filter statement that specifically calls out blank month of closure as something you don't want included in this measure.

    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] ) &&
            ('Actual v Expected'[MONTH_OF_CLOSURE] <> BLANK()) )
    ))

     

Cmcmahan's avatar
Cmcmahan
Resident Rockstar
7 years ago

Try this:

PercentageClosed = COUNTROWS('Actual v Expected')/CALCULATE(COUNTROWS('Actual v Expected'),FILTER(ALLSELECTED('Actual v Expected'), Table[Month]<>BLANK()))

This way you're explicitly filtering out month where the number is blank in your calculation.

Arranafc19's avatar
Arranafc19
Helper IV
7 years ago

Hi Cmcmahan 

 

this didnt work, What I am trying to achieve is to create a measure which calculates the cumulative percentage , but only starting from month one , not including the null values percentage