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

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'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'))