BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mhamlett
Quartz | Level 8

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?

1 ACCEPTED SOLUTION

Accepted Solutions
varsha_sas
SAS Employee

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

3 REPLIES 3
varsha_sas
SAS Employee

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
Quartz | Level 8

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
SAS Employee

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

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