Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi
Need to get sum and max of two columns, but under the condition that the sum and max shall only consider the months in blue.
i have used aggr() combined, but did not got what i expected, thanks
period | Act_Sum | Act_Max |
Jul 16 | 20 | 20 |
Aug 16 | 35 | 35 |
Sep 16 | 42 | 42 |
Oct 16 | 55 | 55 |
Nov 16 | 66 | 66 |
Dec 16 | 71 | 71 |
Jan 17 | 76 | 76 |
Feb 17 | 80 | 80 |
Mar 17 | 82 | 82 |
Apr 17 | 86 | 86 |
May 17 | 92 | 92 |
Jun 17 | 98 | 98 |
YTD Actuals | 152 | 55 |
Using dimensionality() as Sunny mentions in order to calculate rows and totals differently should work and might be the simplest approach.
But if you want to go crazy, you could add new field like "Period Group". Individual months would map to themselves as the Period. YTD would map to all Periods YTD. YTD is then just another row on your chart.
This is a bit more than what you're asking for, but this sort of idea:
May be use Dimensionality() here:
Using dimensionality() as Sunny mentions in order to calculate rows and totals differently should work and might be the simplest approach.
But if you want to go crazy, you could add new field like "Period Group". Individual months would map to themselves as the Period. YTD would map to all Periods YTD. YTD is then just another row on your chart.
This is a bit more than what you're asking for, but this sort of idea:
thanks for the answers, the model is more complex than what i displayed, and i use a dummy variable and text boxes to visualize the totals, in case the user select different fiscal years.
Dimensionality is a good option to solve partial total sums