Forum Discussion
Get global coverage based on conditions
Hello everyone, I would like to seek some advice regarding the following question please.
I have 3 tables: 1 is a big fat table for events of different categories across the globe. The 2nd table is a list of unique countries including country code, country names etc. The 3rd one is a small table for a list of unique categories appeared in the big table. I joined table 1 with table 2 by country code, and table 1 with table 3 by category ID. A sample screenshot for table 1 is below for your reference.
What I would like to do is to use global coverage for categories such as expos and festivals as my benchmarks to measure against same category coverage for each country. I am thinking this can be achieved by:
1. Create a column "Count" and make it all equal to 1. Then create a measure "Total" = count([count])
2. Not sure how to achieve the following:
- Count the total events ("Totals") for when category = festivals and expos (only) BUT excluding the count for when country = US (because US is a huge outlier so anything about US will not be counted in the "Totals")
- Count the total events for a single category (festivals or expos) BUT excluding the count for when country = US (for the same reason as above). This one will get "Total festivals" and "Total expos"
3. Get the % for each category. This one is easy so DIVIDE("Total festivals", "Totals"), DIVIDE("Total expos", "Totals")
Eventually, all the % will be on the same graph (if possible) similar to below:
Please let me know. Thanks!
- Anonymous9 years ago
HI Janet_PHQ,
Perhaps you can try to use below formulas to calculate the percent festivals and expos if they suitable for your requirement.
count of festivals and expos %(exclude US)= DIVIDE(CALCULATE(Count([Count]),FILTER(ALL(Table1),AND([Category]="festivals",[Category]="expos")&&[CountryCode]<>"US")) ,COUNTAX(ALL(Table1),[Category]),0) Count of festivals or expos %(exclude US)= DIVIDE(CALCULATE(Count([Count]),FILTER(ALL(Table1),OR([Category]="festivals",[Category]="expos")&&[CountryCode]<>"US")) ,COUNTAX(ALL(Table1),[Category]),0)
BTW, I found your category column only store single category each row, how did you calculate the count of event who contains multiple categories?
Regards,
XIaoxin Sheng
5 Replies
- AnonymousNot applicable
HI Janet_PHQ,
Perhaps you can try to use below formulas to calculate the percent festivals and expos if they suitable for your requirement.
count of festivals and expos %(exclude US)= DIVIDE(CALCULATE(Count([Count]),FILTER(ALL(Table1),AND([Category]="festivals",[Category]="expos")&&[CountryCode]<>"US")) ,COUNTAX(ALL(Table1),[Category]),0) Count of festivals or expos %(exclude US)= DIVIDE(CALCULATE(Count([Count]),FILTER(ALL(Table1),OR([Category]="festivals",[Category]="expos")&&[CountryCode]<>"US")) ,COUNTAX(ALL(Table1),[Category]),0)
BTW, I found your category column only store single category each row, how did you calculate the count of event who contains multiple categories?
Regards,
XIaoxin Sheng
- Janet_PHQ
Helper I
Hello XIaoxin ,
Thanks for your reply! I will try it soon :)
Each event only has a single category so there is no need to worry about that.
Cheers,
Janet
- mdaamirkhan
Post Prodigy
it is possible to sahre file