Need percent of total based on each of 3 possible values in a column (measure or calculated column?)

[michael95677]

I have a column that has 3 possible values. Yes, No and NA. What I need is a measure (I'm assuming measure as it would apply to the entire tabl) that shows the percentage of the total count for each value. 

The count for total can come from that column or from other columns that will also show the same exact count of visits. 

Ultimately, I'm trying to show a trending graph for the number answered as Yes as a percent over time (3 months for example) for a group of providers as a whole and then break that out individually (some type of slicer to select providers one by one). 

Might need two measures or multiple variables etc. Having trouble knowing where to begin on this. 

Thank you in advance for any help. 

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[michael95677]

Upon further testing the percentages never add up to 100%. I notice that you created a separate calendar outside of the actual dataset. Is this required? Thank you. 

[Ashish_Mathur]

Hi,

Those percentages have been derived from the DISTINCTCOUNT() function so while horizontal additions at the Date level will add up to 100%, foor onth they will not.

[michael95677]

Thank you. Appreciate the clarification. 

[michael95677]

Thank you so much.  Greatly appreciate your help on this. 🙂

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[michael95677]

Basically, I'm pulling about 1-3 months worth of data at a time. I want to trend the total count of Yes as a percent of the total count of the column and No and NS. In this case I provided a small sample of what is around 2,000 rows. For any one encounter date there might be 100 encounters where each encounter has the possibility of a provider answering Yes, No or No Soap (NS). 

I need to be able to show for a week or month etc. each day and how much the Yes percent is for that day, how much the No percent is for that day and how much the NS percent is for that day and then trend that over a week or month etc. Could also be done as totals for a week and compare each week in a month. 

Ideally, the ability to then slice or drill down and get the trend for each individual provider would be the goal. 

Thank you so much for the help. 🙂 

RenderingProvider	ProviderConfirm_ReconcileCheckBox	EncDate
SomeName1	No	07/25/2022
SomeName2	No	07/22/2022
SomeName1	Yes	07/20/2022
SomeName4	Yes	07/20/2022
SomeName5	Yes	07/22/2022
SomeName2	No	08/04/2022
SomeName7	No	08/03/2022
SomeName8	No	07/22/2022
SomeName1	No	07/29/2022
SomeName10	Yes	07/21/2022
SomeName11	No	08/08/2022
SomeName12	No	07/15/2022
SomeName2	Yes	08/15/2022
SomeName14	Yes	07/15/2022
SomeName3	No	07/15/2022
SomeName5	Yes	08/03/2022
SomeName1	No	08/02/2022
SomeName18	No Soap	08/04/2022