topic Showing a data set grand total on each row in a table in SAS Visual Analytics

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

mhamlett — Tue, 21 Jun 2016 20:21:39 GMT

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?

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

varsha_sas — Thu, 30 Jun 2016 18:40:26 GMT

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

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

mhamlett — Mon, 11 Jul 2016 18:13:19 GMT

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

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

varsha_sas — Mon, 18 Jul 2016 16:48:20 GMT

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!