SWITCH in Calculated Column showing line item totals...but I need aggregated totals

[Anonymous]

Hello Community  -  I am using this column formula to place into a shape map  (in the legend).   In the Values area I am putting the measure  (sum of oppotunity value). 

The goal is show on the shape map (countries or states) what the TOTAL value of all opportunities are for a particular area.  

However, this below is not giving me the TOTAL value, but rather only showing the independent opportunities that fall within these buckets.   Take Italy for example.  There is an opportunity worth $5.1 million and so that is the only opportunity value represented on the shape map because it falls into the second criteria below.    However, Italy has a TOTAL of  $8million in opportunities...that is the value that I need the formula to be capturing.   

I guess somehow I need to tell the formula to aggregate at the country level?  

Value Cohorts = SWITCH

(TRUE(),

[Sum of Opportunity Value] <= 1000000,"$0 - $1 Million",

[Sum of Opportunity Value] < 10000000,"$1 Million to 10 Million",

BLANK()

)

[Anonymous]

Thanks @MFelix     Not sure if the best way but I created this table and then used that table in the Switch statement per your idea.   Works great in the shape map.    Using the "country total" in the saturation area, and the cohort column in the legend.

Country Total Table = SUMMARIZE('Sf_Opportunity Line','Sf_Opportunity Line'[Project_Country__c],"Country Total",[Sum of Opportunity Value])

Opportunity Value Cohort = SWITCH(

TRUE(),

[Country Total] <= 1000000,"$0 to $1 Million",

[Country Total] <= 5000000,"$1M+ to $5 Million",

[Country Total] <= 10000000,"$5M+ to $10 Million",

[Country Total] <= 20000000,"$10M+ to $20 Million",

[Country Total] <= 100000000,"$20M+ Million",

BLANK()

)

[MFelix]

Hi @Anonymous ,

When I refered to a temporary table would be on the measure using a variable, and not a new table because this reduces flexibility if you want to add additional filters on the calculation, but this works also.

You could create the cohort table with the values of the ranges and then create the switch similar to what you have and should work also correctly, But glad you were abble to get a working option.

[MFelix · Accepted solution]

Hi @Anonymous 

Using metrics with SWITCH or IF to have a aggregated levels will return incorrect total, in this case without any data is difficult but you need to pick up the data on a temporary table and then make the selection you need.

Can you please share a mockup data or sample of your PBIX file and expected result. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.