Change your calculation to include ATTR for the Segment dimension:
when 'Consumer' then [Sales_Profit]
Since Sales_Profit is an aggregate, you need to aggregate the dimension.
This helped. Thank you!
My apologies, I did not do the mock up correctly before . Please find the attached workbook.
The case I'm trying is little different. With a parameter and calling measures, when i do that I have some aggregates and non- aggregates as in the Calc I've in the workbook ...'Select Segment Type'
It is throwing me cannot mix aggregates and non aggregates. I tried to put sum(sales) and just sales ...but my use case is this. Is there a way that I can make the select segment type work calc work correctly.
I've attached the workbook again. Please let me know.
First, to fix the "Select Segment Type", you were close. You can either add "sum" to the Sales or make both "Aggregate Sales". Either would work:
Are you trying to make the parameter select the segment you want displayed? If that is the case, you can add a Segment Filter:
and place that into the filters on the worksheet. Then when you select the parameter, only the appropriate segment will show, with the appropriate calculation.
Hope this helps!
My use case is ..I need to use both aggregate and non-aggregates in the above case statement same time. I just put profit there two times just to explain my use case.
I need to have :
sum(sales) and just sales in the same case. Is there a way that i can aggregate Just the sales even before bringing into the case statement or do something in the case other than doing (Profit). I'm trying to use both sum(sales) and sales (some how doing this without sum without losing the grain) same time.
I'm a little confused with this. Why are you trying to do a sum and the granular sales within the same calculation? How will the data be displayed?
sorry for the confusion..
I'm not trying to show sales twice.. that is just for an example why my case is failing..It can any other metric...Is there a way that I can change that aggregation to Non- Aggregation , parse the case statement and show the data?
In my actual use case..I've many measures using in the case. of which some are aggregates and some are non- aggregates. I have a/b and some are sum(a)/sum(b) etc. ... I need to find a way to parse them all and control the measure being passed into worksheet using a parameter( and which is done aleady).Since i'm getting can't mix agg and non- agg ..that is where I'm struck
Not sure if i'm making sense?
It appears what you are trying to do is to sum Sales when you select "Home Office", sales/profit ratio when selecting "Consumer" and the granular (non-aggregate) Sales when selecting "Corporate". When you are saying the granular level non-aggregates, what dimensions will be used to get to this granular level?
For example (maybe this is similar to what you are trying to accomplish), let's say that you want to have Category as the dimension when "Corporate" is selected. The report would then look like this:
Sales is at the granular level of Category. So, to them make the report to change the level of granularity, you make the dimension dynamic. This can be done in a calculation based on the parameter:
Then change your "Select Segment Type" to have them all aggregates (the Dynamic Dimension will determine the level of granularity).
Then the parameter will drive the selections: