Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Create custom column to create cumulative histogram

I am looking for support in carrying out the following:

 

1. There is a table with a column that has Revenue plan numbers and details like country, service, etc. BUT no date

2. I would like to create a custom column that will help me calculate the revenue plan numbers in each row in a way that I have the cumulative numbers for each month. For example: If the total revenue plan is 10,000. January will be 10000/12, February will be (10,000/12)*2, March will be ((10000/12)*2)+ (10000/12) and so on until by December it adds up to the Total revenue plan amount of 10,000.

 

Unable to figure out a way to do this. Some help will be really appreciated.

Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    Hi Anonymous ,

    For your requirement, you could create a calendar table firstly.

     

    Calendar = CALENDAR(DATE(2019,1,1),DATE(2019,12,31))

    Then create the measure with SWITCH function to calculate the revenue for each month. You could refer to my expression like below. [SUM ] is also a measure.

     

     

    SUM = CALCULATE(SUM('Table'[Revenue]))
    
reve =
VAR a = [SUM] / 12
RETURN
    SWITCH (
        TRUE (),
        MAX ( 'Calendar'[Date].[Month] ) = "January", a,
        MAX ( 'Calendar'[Date].[Month] ) = "February", a * 2,
        MAX ( 'Calendar'[Date].[Month] ) = "March", a * 2 + a,
            ....,
            MAX ( 'Calendar'[Date].[Month] ) = "December", [your expression],
        Blank()
    )

    Here is my test example, I only calculate for three month.

     

    Hope this can help you.

    Best  Regards,

    Cherry

     

3 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    Hi Anonymous ,

    To be more helpful, please share some data sample which could reproduce your scenario and your desired output so that we could copy and test on it.

    Best  Regards,

    Cherry

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi v-piga-msft ,

       

      I have created a sample data and visualization for you in excel. The formulas that I used to calculate the cumulative in excel are:

      Jan = Total revenue plan / 12 i.e. 280,000/12 = 233,333

      Feb = 233,333*2

      Mar = 466,667 + 233,333

      Apr = 700,000 + 233,333 , and so on

       

      I need to create the same thing on Power BI. Hope the sample helps.

       

      Data sampleVisualization sample

      • v-piga-msft's avatar
        v-piga-msft
        Icon for Resident Rockstar rankResident Rockstar
        7 years ago

        Hi Anonymous ,

        For your requirement, you could create a calendar table firstly.

         

        Calendar = CALENDAR(DATE(2019,1,1),DATE(2019,12,31))

        Then create the measure with SWITCH function to calculate the revenue for each month. You could refer to my expression like below. [SUM ] is also a measure.

         

         

        SUM = CALCULATE(SUM('Table'[Revenue]))
        
reve =
VAR a = [SUM] / 12
RETURN
    SWITCH (
        TRUE (),
        MAX ( 'Calendar'[Date].[Month] ) = "January", a,
        MAX ( 'Calendar'[Date].[Month] ) = "February", a * 2,
        MAX ( 'Calendar'[Date].[Month] ) = "March", a * 2 + a,
                ....,
                MAX ( 'Calendar'[Date].[Month] ) = "December", [your expression],
        Blank()
    )

        Here is my test example, I only calculate for three month.

         

        Hope this can help you.

        Best  Regards,

        Cherry