Totals for Group wise

[paulwalker]

Hi Experts,

User expecting Total for each group level

Let say below is my table,

Sales:
LOAD * inline [
Month, Category, Sales
Jan, Books, 230
Jan, Pens, 100
Jan, Pencils, 120
Jan, Rubbers, 200
Feb, Pencils, 210
Feb, Pens, 300
Feb, Rubbers ,185
];

BAR Chart: This is basic chart but user is asking to show Total for each group like Jan Total and Feb Total...etc

I have concatenating the whole data to the main table with Category as 'Total' and I am getting the correct result as they expected.

Concatenate
LOAD Month,
'Total' as Category,
Sales
Resident Sales;

Here I'm facing issue, when user select in Legend two Category along with the Total (Pen, Pencils and Total)

This Total should change accordingly when the user selects specific categories.

Thanks in Advance!

[Chanty4u]

Create master dimension 

If(Match(Category, 'Books', 'Pens', 'Pencils', 'Rubbers'), Category, 'Total')

Measure 

Sum({<Category=>} Sales)

[paulwalker]

This will not work.

If I exclude a category, how will the user select the category?  (Should not exclude the Category)

[paulwalker]

Experts, Any workaround on this ?

Please do let me know if anybody faced such scenario

[qv_testing · Accepted solution]

Hope this helps!

Sales:

LOAD *, Category as Cat, 1 as Flag;

LOAD * inline [

Month, Category, Sales

Jan, Books, 230

Jan, Pens, 100

Jan, Pencils, 120

Jan, Rubbers, 200

Feb, Pencils, 210

Feb, Pens, 300

Feb, Rubbers ,185

];

Concatenate

LOAD Month, 2 as Flag,

Cat as CatP,  

'Total' as Category,

     Sales

Resident Sales;

Expression would be like 

IF(GetSelectedCount(Category)>0,SUM({<CatP=P(Category)>}Sales)+Sum({<Flag={1}>}Sales),
Sum(Sales))