Cumulative total

[Spetter]

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 percentage from Q3 and Q4 but my measure seems to cumulate also Q1 and Q2.

In the screenshot below I want to see 21,8% in Q3 in the dark column and 47,8% in Q4 in the dark column

Is there someone who can help me to fix this because I am not seeing what is going wrong?

My measure formula is as follows. I think the problem is in the blue part but I think I am really close, not sure though:

Cumulative percentage = 
DIVIDE(
    IF(SELECTEDVALUE(Measuretable[Quarter])>SELECTEDVALUE(Disconnectedtable[Quarter]);
        CALCULATE(SUM(Measuretable[Actuals]);
            FILTER(ALL(Measuretable[Quarter]);Measuretable[Quarter]<=MAX(Measuretable[Quarter])))
    );
    CALCULATE(SUM(Measuretable[Budget]);
        ALL(Measuretable[Quarter])
     )
 )

Many thanks for all the help that can put me in the right direction!

Friendly greets

[Ashish_Mathur]

Hi,

Your question is not clear.  What does the green column respresent?  When you select Q2, why should Q3 show 21.8%.  Please share a dataset and explain the business question.  Once we can compute the correct figures in a Table, creating a visual should be very simple.

[Spetter]

Hi

I have uploaded the file to

https://www.dropbox.com/s/kgojyt35s0i97d1/test.pbix?dl=0

Below you can see the datamodel and how I have used this in the dashboard

 

 

In the chart below the endresult should be instead:

• dark column Q3: 22,2%
• dark column Q4: 22,2% + 30,5%: 52,7%

[Ashish_Mathur]

Hi,

I think the disconnected table is required but that should be the same as the Date Table.  Thereafter, we can try this measure

=CALCULATE([Percentage actuals],DATESBETWEEN(Measuretable[Datum],MIN(Disconnectedtable[Date]),MAX(Measuretable[Datum])))

[Spetter]

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]

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]

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]

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]

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.

[Spetter]

Hi

I have uploaded the file to

https://www.dropbox.com/s/kgojyt35s0i97d1/test.pbix?dl=0

Below you can see the datamodel and how I have used this in the dashboard

 

The green bars is Percentage actuals (=Actuals/Budget). Budget is mapped to 01/01/20xx so when I want to calculate a quarterly % Actuals, I have to remove the filter on Quarter, else the budget will be 0 in Q2, Q3 and Q4

Percentage actuals =
DIVIDE(
    SUM(Measuretable[Actuals]);
    CALCULATE(SUM(Measuretable[Budget]);ALL(Measuretable[Quarter]))
)

Percentage actuals cumulative from Qx is the same measure, but now I want it to be cumulative. The special requirement is:

• that this measure can only be shown in the chart for the quarters after the selected quarter: this is what i did with the orange code below and with a disconnected table but maybe this can be improved? It is however amazing because Quarter is a textfield which contains Q1, Q2, Q3 and Q4. I don't even know how DAX knows how Q3 is greater than Q2 because it is text 🙂 but it works!
• that this measure can only cumulate the "Percentage actuals"for the quarters after the selected quarter. I didn't succeed to make this happen :s

Percentage actuals cumulative from Qx = 
DIVIDE(
    IF(SELECTEDVALUE(Measuretable[Quarter])>SELECTEDVALUE(Disconnectedtable[Quarter]);
        CALCULATE(
            SUM(Measuretable[Actuals]);
            FILTER(ALL(Measuretable[Quarter]);Measuretable[Quarter]<=MAX(Measuretable[Quarter])))
    );
    CALCULATE(
        SUM(Measuretable[Budget]);
        ALL(Measuretable[Quarter])
     )
 )

In the chart below the endresult should be instead:

• dark column Q3: 22,2%
• dark column Q4: 22,2% + 30,5%: 52,7%

The business question is actually something different. I need this technique to calculate a forecast. It is however pragmatic but maybe interesting to share

• Forecast for Q3 will be the cumulative % from Q2 (= selected quarter) + the cumulative % from previous year starting from Q3 until Q3
• Forecast for Q4 will be the cumulative % from Q2 (= selected quarter) + the cumulative % from previous year starting from Q3 until Q4

What this will actual say is when we use as much budget in Q3 and Q4 as previous year, we will go over budget (more than 100% in Q4) or we will still have enough budget.

I hope this info can help to gain insight in the problem. I am happy to answer more questions if necessary!

Friendly greets

[Spetter]

Hi

I have uploaded the file to:

https://www.dropbox.com/s/kgojyt35s0i97d1/test.pbix?dl=0

Below you can see the datamodel and how I have used this in the dashboard

The green bars is Percentage actuals (=Actuals/Budget). Budget is mapped to 01/01/20xx so when I want to calculate a quarterly % Actuals, I have to remove the filter on Quarter, else the budget will be 0 in Q2, Q3 and Q4

Percentage actuals = 
DIVIDE(
    SUM(Measuretable[Actuals]);
    CALCULATE(SUM(Measuretable[Budget]);ALL(Measuretable[Quarter]))
)

Percentage actuals cumulative from Qx is the same measure, but now I want it to be cumulative. The special requirement is:

• that this measure can only be shown in the chart for the quarters after the selected quarter: this is what i did with the orange code below and with a disconnected table but maybe this can be improved? It is however amazing because Quarter is a textfield which contains Q1, Q2, Q3 and Q4. I don't even know how DAX knows how Q3 is greater than Q2 because it is text 🙂 but it works!
• that this measure can only cumulate the "Percentage actuals"for the quarters after the selected quarter. I didn't succeed to make this happen :s

Percentage actuals cumulative from Qx = 
DIVIDE(
    IF(SELECTEDVALUE(Measuretable[Quarter])>SELECTEDVALUE(Disconnectedtable[Quarter]);
        CALCULATE(
            SUM(Measuretable[Actuals]);
            FILTER(ALL(Measuretable[Quarter]);Measuretable[Quarter]<=MAX(Measuretable[Quarter])))
    );
    CALCULATE(
        SUM(Measuretable[Budget]);
        ALL(Measuretable[Quarter])
     )
 )

In the chart below the endresult should be instead:

• dark column Q3 = 22,2%
• dark column Q4 = 22,2% + 30,5% = 52,7%

The business question is actually something different. I need this technique to calculate a forecast. It is however pragmatic but maybe interesting to share 🙂

• Forecast for Q3 will be the cumulative % from Q2 (= selected quarter) + the cumulative % from previous year starting from Q3 until Q3
• Forecast for Q4 will be the cumulative % from Q2 (= selected quarter) + the cumulative % from previous year starting from Q3 until Q4

What this will actual say is: when we use as much budget in Q3 and Q4 as previous year, we will go over budget (more than 100% in Q4) or we will still have enough budget.

I hope this info can help to gain insight in the problem. I am happy to provide more info if necessary!

Friendly greets

[Spetter]

Hi

I have uploaded the file to

https://www.dropbox.com/s/kgojyt35s0i97d1/test.pbix?dl=0

Below you can see the datamodel and how I have used this in the dashboard

The green bars is Percentage actuals (=Actuals/Budget). Budget is mapped to 01/01/20xx so when I want to calculate a quarterly % Actuals, I have to remove the filter on Quarter, else the budget will be 0 in Q2, Q3 and Q4

Percentage actuals = 
DIVIDE(
    SUM(Measuretable[Actuals]);
    CALCULATE(SUM(Measuretable[Budget]);ALL(Measuretable[Quarter]))
)

Percentage actuals cumulative from Qx is the same measure, but now I want it to be cumulative. The special requirement is:

• that this measure can only be shown in the chart for the quarters after the selected quarter: this is what i did with the orange code below and with a disconnected table but maybe this can be improved? It is however amazing because Quarter is a textfield which contains Q1, Q2, Q3 and Q4. I don't even know how DAX knows how Q3 is greater than Q2 because it is text 🙂 but it works!
• that this measure can only cumulate the "Percentage actuals"for the quarters after the selected quarter. I didn't succeed to make this happen :s

Percentage actuals cumulative from Qx = 
DIVIDE(
    IF(SELECTEDVALUE(Measuretable[Quarter])>SELECTEDVALUE(Disconnectedtable[Quarter]);
        CALCULATE(
            SUM(Measuretable[Actuals]);
            FILTER(ALL(Measuretable[Quarter]);Measuretable[Quarter]<=MAX(Measuretable[Quarter])))
    );
    CALCULATE(
        SUM(Measuretable[Budget]);
        ALL(Measuretable[Quarter])
     )
 )

In the chart below the endresult should be instead:

• dark column Q3 = 22,2%
• dark column Q4 = 22,2% + 30,5% = 52,7%

The business question is actually something different. I need this technique to calculate a forecast. It is however pragmatic but maybe interesting to share 🙂

• Forecast for Q3 will be the cumulative % from Q2 (= selected quarter) + the cumulative % from previous year starting from Q3 until Q3
• Forecast for Q4 will be the cumulative % from Q2 (= selected quarter) + the cumulative % from previous year starting from Q3 until Q4

What this will actual say is: when we use as much budget in Q3 and Q4 as previous year, we will go over budget (more than 100% in Q4) or we will still have enough budget.

I hope this info can help to gain insight in the problem. I am happy to provide more info if I still didn't make this clear enough!

[Spetter]

Any tips that can help me in the right direction?

Friendly greets