You can absolutely achieve this whatever your data source. Do you have an example of the raw data?
Great to hear Alex, I actually attached the data source to the original post...did it not come through?
Those things called "Calculated Member"s for cube sources can be represented as "Calculated Fields" for relational-style sources. Alex is correct that relational sources can calculate anything you can from a cube - in fact, cubes are often a bit more limited in that they pre-calculate certain things for you but restrict others.
Your calc is a little more difficult, though, with the simple data structure you've uploaded - your data is already pre-aggregated and duplicated (with "Overall" listed as a color). Calculated Fields are written against either aggregated data (SUMs, AVGs, etc) or row-level data (which you can then aggregate in your viz).
Yes, to James' point, I was assuming that there was some raw data that you were going up against.
If this is your data then calculated fields like these should work:
if [color]<>"Overall" then [Widgets] - this only has a value if color is a color
if [color]="Overall" then [Widgets]
sum([Total]) - sum([Is Color])
How about the attached?
a calculated field like:
CASE ATTR([Color]) WHEN "Overall" THEN SUM([Widgets])-WINDOW_SUM(SUM(IIF([Color]="Overall",0,[Widgets]))) ELSE SUM([Widgets]) END
with the Compute using set to "Color"
and then edit the aliases for the "Color" field, replacing "Overall" with "Other"
This is done in the attached.
Thanks goes to Michael Cristiani for helping with this idea.
Wow gentlemen, this worked like a dream. Thanks so much for putting this solution together. I used it to make stacked bar with "Other" a part of the whole. I did notice that when I attempted to label the segments with a % of total, it doesn't appear to let me have anything other than the sum of the segment. I realize at this point I'm asking for the moon, but if you know of a workaround, then I'm golden!
Sounds like you are looking for an additional calculated field like:
[Calculated Widgets]/WINDOW_SUM([Calculated Widgets])
and again, set this one's Compute using to the "Color" field as as well, as in the attached.
I am humbled. You're hired :) Thanks so much for taking the time.
I have a new question related to the CASE function, with which I am just getting familiarized. Is it possible to use a wildcard in a case function like this? I'm looking to sum up measures from records containing certain values, but want to use a wildcard to pick them out since they're a bit inconsistent.
Nope - CASE does equality comparisons, no wildcards allowed.
Nested If statements will need to used instead.
Interesting; Alex, can you give me an example of how you would nest an If statement in a case function? Let me know if I can provide a scenario, this would be really helpful.
No, you drop case altogether:
From the help:
While an IF statement is used to perform a sequence of arbitrary tests, a CASE statement is used to search for a match to an expression. A CASE statement can always be written as an IF statement, although the CASE statement will generally be more concise.
If you need to include numeric comparisons in your conditions, use a nested IF clause. The CASE function compares strings only. For instance, suppose you want to break the values of the Sales field into three custom categories: one for sales less than 200, one for sales between 200 and 300, and one for sales between 300 and 400. The formula would be:
[Sales]<200 THEN "Low"
ELSEIF [Sales]<300 THEN "Medium"
ELSEIF [Sales]<400 THEN "High"