Creating a Cumulative Line Chart

[Arranafc19]

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 and I am able to plot this no problem.

I now however need to be able to find the cumulative value of the percetages to allow me to plot the rise per month , however I want to exclude the null values from the count. See below my required dataset look :

Can anyone suggest how I should approach this ?

[MFelix]

Hi @Arranafc19 ,

Your information is a little bit vague to give the better answer. Can you please answer the following questions:

• Are the percentages part of your dataset or calculation based on another field?
• On the first image you null month with 40% and month 1 is 5% however on the cumulative image month 1 is 40% and month 2 is 5% why is this difference?
• Can you share  a sample or a mockup of your datafile?

Please see this post regarding How to Get Your Question Answered Quickly:

How to Get Your Question Answered Quickly (courtesy of @Greg_Deckler).

Regards,

MFelix

[Arranafc19]

Hi @MFelix

Apologies , the second dataset is displayed incorrect. the null should be 40% and then the rest should be cumulitave after that.

I am using a measure to calculate the percetage of overall total per month

[MFelix]

Hi @Arranafc19 ,

I notice now that month 10 doesn't have a value but this is not a big deal, and although your information is very reduce I'm assuming you are using a measure like this:

Percentage =
SUM ( Table[Value] )
    / CALCULATE ( SUM ( Table[Value] ); ALL ( Table[Month] ) )

Create the following measure:

Cumulative =
CALCULATE (
    [Percentage];
    Table[Month] <> BLANK ();
    FILTER ( ALL ( Table[Month] ); Table[Month] <= MAX ( Table[Month] ) )
)

This measure need to be adjusted to your model but without any information on how the rest of the model is setup is difficult to give you a better answer check result below:

Regards,

MFelix

[Arranafc19]

hi @MFelix ,

thanks for your help however that didnt quite work.

I am using the below measure to calcaulate the percentage and am calcaulating the month of closure in my sql query.

PercentageClosed = COUNTROWS('Actual v Expected')/CALCULATE(COUNTROWS('Actual v Expected'),ALLSELECTED('Actual v Expected'))

[MFelix]

Hi @Arranafc19,

Is your month column in number or text?

Are you using a date column?

What was the measure you used for cumulative?

And lastly can you share a mockup of your file?

If it's sensitive information please share trough private message.

Regards
MFelix

[Arranafc19]

Hi @MFelix 

I basically have the below columns in my query:

name,

start date,

end date,

month of close (Datediff between end and start if there is one).

Then in power bi , i am grouping this to get a count of the number closures per the month of closure and using a slicer to filter this by the start date. If there is no end date , this is grouping as a null date of closure. I am then calculating the percentage breakdown using the above mentioned measure. 

What I need to do is calucalte the the cumulative percentage , but only starting from month one , not using the null value percentage

[MFelix]

Hi @Arranafc19 ,

Is it possible to share a sample file, if the information is sensitive create a mockup data or send it trough private message.

One other question is about month of close (Datediff between end and start if there is one), what do you mean this is the datediff are you getting a month number or a difference in months?

Regards,

MFelix

[Arranafc19]

Hi @MFelix 

I created a basic mock version of how my pbix looks .

For the month of closure , I am using a datediff function in sql to get the difference in months between start and end date with null being returned if no end date.

You will see in my table I have the calculated percentage breakdown , I just need to make this cumulative , not including values that have a null month of closure

https://drive.google.com/drive/folders/1VyPBz5Vx5W3NuOsyvGcQgfnPhbUhDAOf

[MFelix]

Hi @Arranafc19 ,

It's asking for password to get the file.

Regards,

MFelix

[Arranafc19]

hi @MFelix 

try again there 

https://drive.google.com/open?id=1knzIBVarM9BwGnnaQswrVLNl-uvhoWXG

[MFelix]

Still asking permission.

Regards,

MFelix

[Arranafc19]

Hi @MFelix 

Are you able to access it now ?

[MFelix]

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

[Arranafc19]

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:

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.

[MFelix]

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