Please provide a sample workbook.
If I would have to guess without a workbook, I would say you might need an ATTR() around [Region Code] as you probably get a "cannot mix agg and non-agg expressions in a CASE". I also assume [Consumer Count] is an agg e.g. COUNTD() so you can't aggregate further with a SUM()
A sample data will be helpful for us to dig down your issue
Meanwhile you may try this
CASE ATTR([Region Code (Registered)]
WHEN 'GB-ABD' THEN SUM([Consumer Count])/SUM([Sheet2 (Test data)].[Consumer Count])
ELSE NULL //Edit the alias and rename it as Unknown
I have attached the workbook. What I am trying to achieve is:
I currently have 4 artists and their listener numbers. I want to figure what % of listeners are for artist X in a given region. So I'm trying to do number of listeners for artist X in given region / total listeners for that region. But I'm not sure the best way to do this.
Does that make sense?
Chris, that workbook isn't very helpful: you don't have a "Sheet 2" datasource already in the workbook, so we can't see what you're trying to do. The sheet is there in the raw data but it's not available to be used for calculations.
It would also be useful to know which error(s) specifically you are getting when you create that calculation.
thats a different problem not just the case.
At first glance, if I understand correctly if you do a SUM([Consumer Count]) / TOTAL(SUM([Consumer Count])) and filter on the artist you want, you should see on the map the percentage per region for that artist... or did you mean percentage from total including other artists?
Thanks for the workbook.
As your two data sources only have one key -- region to connected, so your aggreation only can be in the same level, you can't drill down to "region code (registered)" level, eventhough, in your map, your polygon can be sub polygon level, but the calculation still keeps in the region level.
Assume your level of aggration is in region level, and then how will you determine which artist to select, I recommand to use a parameter which contains a list of all artist names, and below calculation shows how to calculate the % of the selected artist in each region on the map.
Workbook is attached as well for your reference.
Hope this helps
ZZ, I think this is perfect - thanks a lot for your help!
No worries, hope this helps