How do I create a dynamic percentage based on what I select in a slicer?

[dmoskowitz]

I have two text fields, "Due Date Bucket" and "IA Testing Status".

I would like to create a Card that shows the combined percent of the options I select in the IA Testing Status slicer and divide it by what I select in the Due Date Bucket.

For example, if I select "Complete" in the Due Date Bucket slicer" and then I select "Closed Per Methodology" and "Testing In Progress" in the IA Testing Status slicer, it should count the total number of records for "Closed Per Methodology" and "Testing In Progress" and divide it by the total number of records from the Complete slicer.  If this is too complicated, I am fine using measures for this if it is easier (like a measure that has the total count of "Complete" from the Due Date Bucket) and then a slicer where I can select the options in the IA Testing Status slicer.  These fields are independant of each other so not sure if slicers can be used for both fields.

I tried by creating all measures and figured I would just do one divided by the other but felt it was all hard coded and didn't seem to work (it showed 0%)

BU Remediation Status Complete = COUNTROWS(filter('Findings Report (Srch Results)','Findings Report (Srch Results)'[Due Date Bucket]="Complete"))

IA Test Status selected = COUNTROWS(filter('Findings Report (Srch Results)','Findings Report (Srch Results)'[IA Testing Status] IN {"Closed per Methodology","Testing in Progress"}))

IA Remediation Testing Status % = CALCULATE(DIVIDE([IA Test Status selected],[BU Remediation Status Complete]))

Thank you for your help

[parry2k]

@dmoskowitz share using onedrive/google drive or other sharing services.

[parry2k]

@dmoskowitz usually when it gets to this point, I recommend sharing the pbix file with sample data and expected output, otherwise, we will keep going back and forth. 

[dmoskowitz]

@parry2k 

I 100% agree.  It's not giving me the option to attach data and the PBI file though. I need to figure out where to upload it and share a link to a cloud folder or something.

[parry2k]

@dmoskowitz change ALL to ALLSELECTED in my measure

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@dmoskowitz try these measures:

Record Count = CUNTROWS ( 'Findings Report (Srch Results)' )

BU Remediation Status Complete = 
CACLULATE ( 
    [Record Count], 
    ALL ( 'Findings Report (Srch Results)' ), 
    VALUES ( 'Findings Report (Srch Results)'[Due Date Bucket] )
)


 
IA Test Status selected = 
CACLULATE ( 
    [Record Count], 
    ALL ( 'Findings Report (Srch Results)' ), 
    VALUES ( 'Findings Report (Srch Results)'[IA Testing Status] )
)
 
IA Remediation Testing Status % = 
DIVIDE ( 
      [IA Test Status selected],
      [BU Remediation Status Complete]
)

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[dmoskowitz]

@parry2k 

Something is still off.

I removed the slicer for Due Date Bucket because it was not working properly. For total percent of Complete versus total I did the below and it correctly adjusts based on Page Filters.

Due Date Bucket Complete = COUNTROWS(filter('Findings Report (Srch Results)','Findings Report (Srch Results)'[Due Date Bucket]="Complete"))

and used this measure

% Findings = DIVIDE([Due Date Bucket Complete],[Record Count])

I updated the ALL to ALLSELECTED and still not working. The percent should be 32/81 which is 39.5%. Even when I put the values in seperate cards it is showing the raw count correctly.

IA Remediation Testing Status % = DIVIDE (

[IA Test Status selected],

[Due Date Bucket Complete]

)

The percentage is not running correctly though. I'm not sure why record count shows 32, it's really 112 which is 72.32% (81/112).

The 139% is the IA Remediation Testing Status % measure

Thank you again for helping out. This has been bothering me for awhile and seems like it should be straighforward 😞

[v-kelly-msft]

Hi  @dmoskowitz ,

Try:

Record Count = CUNTROWS ( 'Findings Report (Srch Results)' )

BU Remediation Status Complete = 
CACLULATE ( 
    [Record Count], 
     'Findings Report (Srch Results)' [Due Date Bucket] in FILTERS('Findings Report (Srch Results)'[Due Date Bucket])
)


 
IA Test Status selected = 
CACLULATE ( 
    [Record Count], 
      'Findings Report (Srch Results)'[IA Testing Status] in FILTERS( 'Findings Report (Srch Results)'[IA Testing Status])
)
 
IA Remediation Testing Status % = 
DIVIDE ( 
      [IA Test Status selected],
      [BU Remediation Status Complete]
)

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[dmoskowitz]

@parry2k 

Is there a way create a percentage where my total number of records (denominator) is based on Page Filters only? It is similar to the what I am trying to do above. I want to create the percentage of Due Date Bucket Complete relative to all the records but I want the total records to be based on page filters so I can for example filter the year to 2021 and other fields, I would want the percent of "Complete" to Total be based on the filtered records.

The same thing needs to work for the above solution provided where the total records needes to be based on set filters and then the percentage (numerator) based on selected slicers.

I started with a measure to hard code the complete status but not sure how to do the rest.

Thank you

Due Date Bucket Complete = COUNTROWS(filter('Findings Report (Srch Results)','Findings Report (Srch Results)'[Due Date Bucket]="Complete")).