4 Replies Latest reply on Oct 2, 2017 9:29 AM by Hari Ankem

# Aggregated Total without dimension

Hi Folks,

i have the following data set

Fiscal Quarter
Revenue
FY17'Q34500
FY17'Q47500
FY18'Q115000
FY18'Q290000
FY18'Q3

I want aggregated total at fiscal quarter level where when i select FY18'Q3 in filters, all the total from the previous quarters get added up.and show 117,000

I tried doing this with Total() and Running_SUM() funciton.

However, in my sheet, i cannot show Fiscal quarter as a dimension and Total () and Running_SUM() functions only works when there is a dimension against them in the sheet.

Let me know if you need further information.

• ###### 1. Re: Aggregated Total without dimension

Why don't you use a parameter? You don't need to use the dimension then.

Check out the attached workbook if this solution works for you.

• ###### 2. Re: Aggregated Total without dimension

Here is a trick that could be useful in many other scenarios.

Table calculation filters hide marks without changing the results. First you can add an ending date filter for filtering all the data up to FY18 Q3.

Now make sum(sales) a running sum.

Then add a calculation that has the following formula which checks if a given row is the last row.

Add it onto filter shelf and check True so you only keep the last mark which is the value for the last record.

As a result of which you will end up with this. Result of the running sum as if nothing has been filtered since table calculations just hid the marks not really filtered out the input data for the calculation itself.

Now we need to get rid of the date field. From the context menu uncheck show header option.

And this is what you'll get. the result of the running sum but no dates shown. Just the value.

1 of 1 people found this helpful
• ###### 3. Re: Aggregated Total without dimension

Thanks for the solution but there is no workbook attached for taking reference.

Would be great if you can re share that.

• ###### 4. Re: Aggregated Total without dimension

Check out the workbook in the main thread. You will not see the attachment in your inbox.