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 ,

 

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

      • MFelix's avatar
        MFelix
        Super User
        7 years ago

        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