Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have data such as the below.
Continent | Country | Metric |
North America | Canada | 400 |
North America | Mexico | 100 |
North America | USA | 200 |
South America | Argentina | 200 |
South America | Brazil | 300 |
South America | Venezuala | 300 |
On my sheet I have made made the below table with a Country's % of Continent measure with expression sum(Metric)/sum(Total <Continent>Metric) which yields the desired output.
However I want to be able to filter for an individual country and have the % stay the same while also filtering the other countries out of the table.
Currently if I filter on country the % changes to 100%
If I update my formula to sum(Metric)/sum(Total <Continent>{<{Country}>}Metric) then the % is correct but the other Countries are not filtered out when I make my selection.
Does anyone have any ideas, I want to do this with a formula and not by adding a field in me data model that is the continents total value.
QVF attached
Thanks,
Mark
@mjperreault try below
=sum(aggr(sum({<Country,Continent>}Metric)/sum(TOTAL<Continent>{<Country,Continent>}Metric),Country,Continent))*Avg(1)
Try sum(Metric)/sum({1}Total <Continent>Metric) and suppress zeroes on the chart.
Thanks for the suggestion, that would yield this
I do not want to remove the Continent total from the table. Would prefer not to accomplish this with table settings as well but strictly using a formula but not sure if its possible.
Thanks,
Mark
@mjperreault try below
=sum(aggr(sum({<Country,Continent>}Metric)/sum(TOTAL<Continent>{<Country,Continent>}Metric),Country,Continent))*Avg(1)