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 "creat...
Need Help
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

  • thom_vee's avatar
    thom_vee
    Frequent Visitor
    8 years ago

    I created a new column with @1@ as count.

    Then I edited your formula for my headers:\

    Cumulative = CALCULATE ( SUM ( observations[count] ), FILTER ( observations, observations[created_on].[Year] <= EARLIER ( observations[created_on].[Year] ) ) )

    But I seem to have messd up somewhere. The figures are totalling way above the expected count of 1325544.: (it goes into billions!) . Also 2017 is less than 2016.

     

    What am I doing wrong?

     

     

    • thom_vee's avatar
      thom_vee
      Frequent Visitor
      8 years ago
      v-yulgu-msft please could you take a look and help?