Sum(total) not working as expected for multiple selections...

[#1]

Hi All,

It's an issue that I'm trying to work through for a considerable period of time but no luck.

I'm trying to use sum(total) to set a flag and ignore one of the dimensions but respect all selections include MULTIPLE ones (on the calendar etc)

Table is as follows:

Competing, Prod. Group, Product Family, Sales, Total Market.

Formula is:

sum(TOTAL{$<Competing={Competing},Product_Family>}[TOTAL])

It seems to work correctly when I make a single selection for a month or year etc - as on the first screenshot. It sets Competing flag correctly, then ignores Product_Family, and does the by selection.

Unfortunately when having multiple selections for a month / year etc it sums up TOTAL field for all selections rather than BY selection. This is illustrated on the second screenshot.

It still ignores family dimension which is correct, but shows Total_Market value as a sum of month 11 and month 12 rather than by month. Same if I select multiple years on the calendar etc.

I would expect it set the flag (Competing) then ignore Product_Family dimension but respect all other combination of selections / dimensions just as normal sum would.

Would greatly appreciate some help on this as I've been tearing my hair out for days now.

Kind regards

[#2]

What is the name of the field/measure for the SUM?

Try this out..

sum(TOTAL<Year,Month>{$<Competing={Competing},Product_Family>}[TOTAL])

So, that all dimensions are ignored, but not the Year and Month as you want total by year/month. Let us know, if it works.

[#3]

Hello,

Thank you very much for your reply . Unfortunately changing formula to the above didn't have any effect. It still provides a SUM of Total values for all elected period (in case of multiple selections).

See attached screenshots

1. uses the formula above while selecting only 2015 December. - sum of Total Market is correctly displayed as 1,204,760.

2. uses same formulat while having 2015 - Nov and Dec selected. - sum of Total Market is incorrect as it shows 2,289,412 for BOTH months which is a sum of November and December. It should display 1,204,760 for December and 1,084,652 for November.

This also happens when selecting multiple years on the calendars. I only need it to ignore "Product Family" field - with "Competing" variable being enforced regardless of selection. Everything else should be respected.

Kind regards

[#4]

Anyone? Still wrestling with the issue.

Kind regards

[sunny_talwar]

Can you try this:

Sum(TOTAL<[Competing], [Prod. Group], [Product Family]>{$<Competing={Competing},Product_Family>}[TOTAL])

It seems that you were doing a overall TOTAL whereas you don't want to total by Month and Year. If that is the case you need to specify which fields do you want to total by. Now when you have not selected anything, you will should see a total based on Summation of Competing, Prod. Group and Product Family, but this will differ from period to period (month and year)