Forum Discussion

Janet_PHQ's avatar
Janet_PHQ
Icon for Helper I rankHelper I
9 years ago
Solved

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!

  • Anonymous's avatar
    Anonymous
    9 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

  • Anonymous's avatar
    Anonymous
    Not 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's avatar
      Janet_PHQ
      Icon for Helper I rankHelper 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

    • Janet_PHQ's avatar
      Janet_PHQ
      Icon for Helper I rankHelper I

      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!