Forum Discussion

timward10's avatar
timward10
Icon for Helper II rankHelper II
1 year ago
Solved

Compliance reporting - Formula

Hi, 

 

I have the below table, I want to show as a percentage and by region, who is compliant. 

 

For example ANZ has three people, they each need to hit a bluesheet = yes number of 3 to be 100% compliant. If 2 out of the 3 hit, they are 66%, 1 out of three hit 33% compliant. 

 

Can anyone help me please! 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi timward10 

    Please try the following Measure :

    Measure = 
VAR _select_region = SELECTEDVALUE('Table'[Region])
VAR _count_3 = CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[Bluesheet = Yes] = 3 && 'Table'[Region] = _select_region))
VAR _count_region = CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[Region] = _select_region))

RETURN

_count_3 / _count_region

     

    Result:

     

     

     

     

     

    Best Regards,

    Jayleny

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • rohit1991's avatar
    rohit1991
    Icon for Super User rankSuper User
    1 year ago

    Hi timward10 ,
    Based on your table, we can calculate compliance percentages by region by checking how many owners meet the "Bluesheet = Yes" threshold of 3. For each region, we count how many owners hit that threshold and divide it by the total number of owners in that region.

    For example, in ANZ, you have four owners (Ann Serpell, George Koukides, Moveen Narayan, and Manish Pandit). If none of them reach 3 bluesheets, compliance is 0%. If 1 out of 4 reaches it, compliance is 25%, and so on.

    Looking at your table, it seems Benelux has two owners meeting the threshold, making it 100% compliant. Meanwhile, ANZ and Asia Pacific show 0% compliance since no one hits the required bluesheets.

     

    • timward10's avatar
      timward10
      Icon for Helper II rankHelper II
      1 year ago

      Hi rohit1991 

       

      That is correct, but how to a demonstrate that through a formula and then represent it in the table? 

       

      Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi timward10 

    Please try the following Measure :

    Measure = 
VAR _select_region = SELECTEDVALUE('Table'[Region])
VAR _count_3 = CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[Bluesheet = Yes] = 3 && 'Table'[Region] = _select_region))
VAR _count_region = CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[Region] = _select_region))

RETURN

_count_3 / _count_region

     

    Result:

     

     

     

     

     

    Best Regards,

    Jayleny

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.