Showing a data set grand total on each row in a table

[mhamlett]

my data is meals for each restaurant by districts, years, months

i have in va a  table with these values listed filtered by a section filter "year".

i have created an aggregated measure to show meals total "for all"

of course when i initially show the rows in the table the meal count is correct...all stores, for selected year, all districts

if i filter by district, then the meal count changes to not be all districts, but just the selected district.

how do i create an aggregated measure to always be the grand total and not some subsetted total?

[varsha_sas]

Hi, I just wanted some clarification on what exactly you're trying to do. From my understanding: you want a list table or crosstab with filters. Do you have several measures that you want filtered and then a total number that you want to stay static, regardless of the filter? Does this number have to appear in the list table or crosstab at the bottom or top? Also, do you have a sample of your data that you can take a screenshot of and post?

Thanks!

Varsha

[mhamlett]

lets make this simple...

data

customer         monthly sales     month

abc                  100.00               january

abc                  200.00               february

abc                  50.00               March

def                  200.00               january

def                  300.00               february

what i want to show in VA is

abc                 100.00              january              850.00      12%

abc                 200.00              feb                    850.00       24%

abc                  50.00              march                850.00        6%

def                 200.00              january              850.00      24%

def                 300.00              february             850.00      35%

how do i calculate the $850.00

[varsha_sas · Accepted solution]

Ok, I have a solution for this. You'll need to create two aggregated measures and add these to the crosstab. I called one of these "Grand Total", which will give you the 850 in each row and the other one "Grand Total Percent", which will give you the percentages in each row. The exact calculations can be found in the screenshots below. Please let me know if you have any other questions based on this.

Thanks!