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

 

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's avatar
MFelix
Super User
7 years ago

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's avatar
    Arranafc19
    Helper IV
    7 years ago

    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's avatar
      MFelix
      Super User
      7 years ago
      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's avatar
        Arranafc19
        Helper IV
        7 years ago

        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