Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

cumulative percentages

Hi!

 

I need to calculate the cumulative percentage as in the file attached.

 

What I need is to have another column with cumulative percentage with 25%, 62,5%, 87.5%, 100%.

 

Any idea how to do it? I checked this thread but couldn't make it.

 

Thanks

 

Cheers,

Andrea

Need Help

17 Replies

  • v-danhe-msft's avatar
    v-danhe-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi Anonymous,

    You could refer to Miltinho's solution, see below code:

    Measure = 
var sales=CALCULATE(SUM('TEST'[Sales]),FILTER(ALLSELECTED(TEST[Collection]),ISONORAFTER('TEST'[Collection],MAX('TEST'[Collection]),DESC)))
var totalsales=CALCULATE(SUM('TEST'[Sales]),ALLSELECTED(TEST))
return DIVIDE(sales,totalsales)

    Result:

    You could also download the pbix file to have a view.

     

    Regards,

    Daniel He

     

    • AliceW's avatar
      AliceW
      Icon for Power Participant rankPower Participant
      2 years ago

      Can confirm it still works and it's still needed! 🙂 Thanks, man!

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    Hi,

     

    To compute the cumulative %, how does one know the order to be followed.  Looking at your data, the order is definitely not the Sales.  Is the order the Collection column - Autumn, Spring, Summer and then Winter?

     

    Please clarify.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      HI Ashish_Mathur, exactly, the order is the collection.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        7 years ago

        Hi,

         

        Here's what i did.  I first wrote this calculated column formula to get the indexing by season

         

        Order of seasons = if([Collection]="Autumn",1,if([Collection]="Spring",2,if([Collection]="Summer",3,4)))

         

        I then wrote these measures

         

        Revenue = SUM(TEST[Sales])
        Season order = if(HASONEVALUE(TEST[Collection]),SUM(TEST[Order of seasons]),BLANK())
        Cumulative revenue = SUMX(TOPN([Season order],CALCULATETABLE(VALUES(TEST[Collection]),ALL(TEST[Collection])),[Season order],ASC),[Revenue])
        Total revenue = CALCULATE([Revenue],ALL(TEST[Collection]))
        Cumulative revenue/Total revenue = [Cumulative revenue]/[Total revenue]
         
        In the visual. i dragged Revenue and Cumulative revenue/Total revenue.
         
        Hope this helps.
         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi great_AS,

    See this image and tell us if works for you.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      HI Anonymous!

       

      That's perfect!! Can you please share your formula here so I can copy and paste it?

       

      Thanks.

       

      Cheers,

      Andrea

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Andrea, I solved it then I deleted it.

  • Maldarraji's avatar
    Maldarraji
    Frequent Visitor
    5 years ago

    Hi All can any one help to calculate cumulative for (allocation_rat) column ?

     

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      5 years ago

      Hi,

      You should have a Calendar Table with a relationship (Many to One and Single) from the Allocation_date column of the Data Table to the Date column of the Calendar Table.  From your visual, remove the Allocation_date column and drag the Date column from the Calendar Table.  Assuming allocated_rate is a measure, write this measure and drag it it your visual

      YTD allocate_rate = calculate([allocated_rate],datesytd(calendar[date],"31/12"))

      The YTD calculation will restart on January 1 of every year.

      Hope this helps.

      • Maldarraji's avatar
        Maldarraji
        Frequent Visitor
        5 years ago

        Hi Mr. Ashish, I'm a new intern at The PowerBI, and I don't know how to get the Cumulative when I worked the Calender they give me error. could you please help to create table have cumulative rate starting from 01/01/2015  to 30/12/2021