Get global coverage based on conditions

[Janet_PHQ]

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!

[v-shex-msft · Accepted solution]

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]

Hi Xiaoxin,
 
Thanks for your advice earlier!
I accepted the solution 🙂
 
Can I ask you another related question please?Refer to my question here:

Have a good weekend!

[mdaamirkhan]

it is possible to sahre file 

[Janet_PHQ]

Thanks, please visit my new post here:

http://community.powerbi.com/t5/Desktop/Re-Get-coverage-filtered-by-country-and-category/m-p/225368/...

[Janet_PHQ]

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