How to Group dimension memebers in calculation in SAC?

[azm_azm]

Hi,

Here is the scenario.

There is total ten brands in my brand dimension.

For Example, Brand A, Brand B, Brand C .... Brand J.

Now, I want to create a report where I just want to see only top three brands and group other brands as "Other". The calculation of "Other" Brand should be [ Total Sales of All Brands - Sum of Top Three brands] .

Please refer below screenshot.

Anyone who has faced similar kind of issue or knows the workaround in this case. Their comments would be deeply appreciated.

Thanks

[N1kh1l]

azm_azm

Please see response below.

Is it really possible to use R visualization and get the result populated in table in SAC? - Yes

Example below based on sample data. Adjust according to your model.

Configuration:

Add R Visualization to the story page

Selected my Model and in Table structure Selected products in Rows and the Measure (Quantity Sold in column)

R Script:

# Import the required packages
library("dplyr")
library("plotly")
library("flextable")
library("officer")
# Creating data frame for processing
df<-BestRunJuice_SampleModel
df1=data.frame(Product=(df$Product), QuantitySold=c(df$`Quantity sold`))
df2 <- df1 %>% group_by(Product) %>% summarise(QuantitySold = sum(QuantitySold)) %>% arrange(desc(QuantitySold)) 
# Checking Top 3
if(nrow(df2) > 3) 
{ 
  df2 <- rbind(top_n(df2,3), slice(df2,(4):n()) %>% summarise(Product="Others", QuantitySold =sum(QuantitySold)))
}
df2 = rbind(df2, data.frame(Product = "Totals", QuantitySold = sum(df2$QuantitySold))) 

# Creating the Table for presentation
 ftab <- flextable(df2)
 ftab <- bold (ftab, i = ~ Product %in% "Totals" , bold = TRUE)
 ftab <- autofit (ftab)
 ftab <- theme_vanilla(ftab)
 ftab <- fontsize(ftab, size = 13, part= "all")
 ftab <- autofit (ftab)
 ftab <- set_header_labels (ftab, Product = "Products", QuantitySold = "Quantity Sold") 
ftab 

Read more about the packages:

https://mran.revolutionanalytics.com/snapshot/2019-02-07/web/packages/flextable/vignettes/overview.h...

https://dplyr.tidyverse.org/

Output:

Hope this helps !!

Please upvote/accept if this helps you

Nikhil

[horynao]

Hello,

I didnt find some way to set up it in SAC directly. One workaround would be R lang visualization, but it is little bit difficult. See the link below. Another way could be to pre-calculate choosen dimension before it is loaded to SAC, create dimension with top n values and "other" value, create its own model and link it to main one. But this could have a lot of obstacles.

https://stackoverflow.com/questions/70304967/how-to-select-top-n-values-and-group-the-rest-of-the-re...

Best regards,

Ondrej

[azm_azm]

Thank you horyna.

Is it really possible to use R visualization and get the result populated in table in SAC?

Adding another model and then linking will add complexity in the design and security maintenance.

Let's just hope SAC comes up with this functionality in future updates.