Forum Discussion

thom_vee's avatar
thom_vee
Frequent Visitor
8 years ago

Cumulative growth histogram

Hi All,

I just got introduced to Power BI and have been testing it out. I`ve been creating some visualisations using it. 

I have a table called observations , in which I have a column called "created_on". I have charted out a bar graph of no of observations per year using this colum. However, I wish to make a histogram showing the cumulative growth over the years. I have tried googling and there seem to be may discussions on this but I just can figure out what exactly I need to do.

 

Please help!

 

Need Help

20 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi thom_vee,

     

    Could you please illustrate "showing the cumulative growth over the years" with more details? Do you want to calculate the growth every two years? If so, please refer to below formulas to create calculated columns.

    Count TY =
CALCULATE (
    COUNT ( Sheet1[Created on] ),
    ALLEXCEPT ( Sheet1, Sheet1[Created on].[Year] )
)

Count LY =
CALCULATE (
    COUNT ( Sheet1[Created on] ),
    FILTER (
        Sheet1,
        Sheet1[Created on].[Year]
            = EARLIER ( Sheet1[Created on].[Year] ) - 1
    )
)

Growth = IF(Sheet1[Count LY]=BLANK(),0,(Sheet1[Count TY]-Sheet1[Count LY])/Sheet1[Count LY])

    Use a Line and Clustered column chart to display data.

     

    Best regards,
    Yuliana Gu

    • thom_vee's avatar
      thom_vee
      Frequent Visitor
      8 years ago

      Thank you for the reply Yuliana. I wish to count the cumulative growth every year.For eg:

       

      Created on

      09-03-2017: 16:26:00

      01-06-2016: 16:26:00

      03-04-2016: 16:26:00

      06-03-2015: 16:26:00

      04-11-2016: 16:26:00

      02-08-2016: 16:26:00

      31-12-2015: 16:26:00

      27-04-2014: 16:26:00

       

      so the count every year:

      2014 - 1

      2015 - 2

      2016 - 4

      2017 - 1

       

      What I want is  cumulative addition of the previous years:

      2014 - 1

      2015 - 3

      2016 - 7

      2017 - 8

       

      If I can generate this coulum I can plot it either as bar or histogram.

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi thom_vee,

         

        Add a calculated column, set its value to 1.

        Number = 1

        Create a  cumulative calculated column.

        Cumulative =
CALCULATE (
    SUM ( Sheet1[Number] ),
    FILTER (
        Sheet1,
        Sheet1[Created on].[Year] <= EARLIER ( Sheet1[Created on].[Year] )
    )
)

         

        Best regards,
        Yuliana Gu