Forum Discussion

Spetter's avatar
Spetter
Advocate IV
8 years ago

Cumulative total

Hi,   I am making a cumulative percentage and I am using the DAX pattern to do this. However, I need a slight customization. If I select Q2 I want the cumulative percentage to cumulate the percent...
Need Help
Spetter's avatar
Spetter
Advocate IV
8 years ago

Hi

 

I tried the solution:

  • made a new disconnected table with all the dates
  • made a new measure and put it on the chart
  • made a new slicer with "quarter" from the new disconnected table

 

but it doesn't seem to work (see red columns in the screenshot below)

The red columns seem to show the same "Percentage actuals"

Am I doing something wrong?

 

New pbix file:

https://www.dropbox.com/s/myszmnvqg9m7u93/test2.pbix?dl=0

 

 

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi,

 

I have not been able to solve the problem.  I get the correct answer only when i select Q1 in the chicklet slicer.  May be someone else can help you from here.  You may download the PBI file from here.

 

  • Spetter's avatar
    Spetter
    Advocate IV
    8 years ago

    Hi

     

    The reason that Q2, Q3 and Q4 doesn't work probably has to do with that the budget is all in Q1 on date 01/01/20XX and DATESBETWEEN can't see the budget in Q1 if you select Q2, Q3 or Q4 so it can't calculate the percentage.

     

    Thanks for thinking with me and trying to make up a solution. We probably need something else then DATESBETWEEN.

     

    Is there anyone else who thinks to know how a cumulative % can be calculated only after a selected period?

    I would really really really be grateful for any help!

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      8 years ago

      Hi,

       

      If someone selects Q2 in the slicer, then in the Table do you want to compuate the cumulative for Q3 as Q1+Q2+Q3 or should it be Q2+Q3?

      • Spetter's avatar
        Spetter
        Advocate IV
        8 years ago

        When a user selects Q2 in the slicer it should calculate the percentage for Q3 in the chart for quarter 3 and it should calculate the percentage for Q3+Q4 in the chart for quarter 4.

         

        But the calculation for "% actuals" from Q3 = Actuals Q3/Budget Q1

        and the "% actuals" from Q4 = Actuals Q3/Budget Q1 + Actuals Q4/Budget Q1

         

        I just need the cumulative % to start after the selected quarter but for some reason it also cumulates the % actuals for Q1 and Q2 in quarter 3 and quarter 4 :s

         

        I thought I was close but the IF-statement only works for not showing the % in Q1 and Q2 and doesn't work to let the cumulative % start from after Q2.