Exploring, modeling, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

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

Accepted Solution Solved
Reply
Contributor
Posts: 59
Accepted Solution

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

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?


Accepted Solutions
Solution
‎07-18-2016 01:37 PM
SAS Employee
Posts: 29

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

[ Edited ]

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!


GrandTotal.pngGrandTotalPercent.pngFinalResult.png

View solution in original post


All Replies
SAS Employee
Posts: 29

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

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

Contributor
Posts: 59

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

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

 

Solution
‎07-18-2016 01:37 PM
SAS Employee
Posts: 29

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

[ Edited ]

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!


GrandTotal.pngGrandTotalPercent.pngFinalResult.png
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 405 views
  • 0 likes
  • 2 in conversation