Forum Discussion

Arranafc19's avatar
Arranafc19
Helper IV
7 years ago

Creating a Cumulative Line Chart

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

Hi MFelix 

 

Are you able to access it now ?

MFelix's avatar
MFelix
Super User
7 years ago

Hi Arranafc19 ,

 

Try the following measure:

Cumulative % = 
CALCULATE (
    CALCULATE (
        COUNTROWS ( DealerData );
        FILTER (
            ALL ( DealerData[MonthOfSale]; DealerData[Dealer] );
            DealerData[MonthOfSale] <= MAX ( DealerData[MonthOfSale] )
        );
        DealerData[MonthOfSale] <> BLANK ()
    )
)
    / CALCULATE ( COUNTROWS ( DealerData ); ALLSELECTED ( DealerData ) )

Based on the context of your table I had to redo the calculation in the cumulative.

 

Be aware that if you add another field for context like the dealer the formula will give repeated values for each of the months 

 

Check result in PBIX file attach.

 

Regards,

MFelix

  • MFelix's avatar
    MFelix
    Super User
    7 years ago

    Hi Arranafc19 ,

     

    You are missing the blank filter on your measure try to rephrase to this:

     

     

     

    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] )
    ),
    DealerData[MonthOfSale] <> BLANK ()
)

     

    Regards,

    MFelix

     

  • Arranafc19's avatar
    Arranafc19
    Helper IV
    7 years ago

    hi MFelix ,

     

    this didnt work for me , however i have managed to get this far ..

     

    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:

     

    Capture.PNG

    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:
     
    Capture2.PNG
     
    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.