Percent of Column Total in Line Chart as a Part of Switch Formula

[amila]

Hi Everyone,

I am quite new to Power BI so apologies if this is an easy one, or if it has already been answered. 

I have the below data structure containing Patient IDs with the information about their status regarding the treatment in question:

INDENTIFIER	STATUS
411147	Completed
411292	Completed
413951	Dropped out
414301	Dropped Out
411147	Dropped out
413951	In progress
411292	In progress
411581	In progress
411671	In progress
414321	Not Initiated
411147	Not Initiated
411581	Not Initiated

I am trying to create a line chart with the possibilty to switch between two different views:

1) COUNT: showing the count of subjects for each of these statuses over time as a separate line

2) PERCENT: showing each status as a percentage of the total, also over time

So far, I have created the following two measures:

CountID= DISTINCTCOUNT(Table[IDENTIFIER])

PercentID = ?

Then I created a new table to use as a slicer, changing the view between COUNT and PERCENT with the following formula:

Switch = SWITCH([Selected TableParameter],1,Table[CountID],2,Table[PercentID],BLANK(),[CountID])

Everything works fine, except for the percentage formula. I have tried several options using DIVIDE, but nothing worked for me. I also tried to select "percent value of total" in the Visualizations section, but that just gave me simple 25% for each of the values. I was wondering if someone could help me figure what formula could be used to calculate the number of each of the STATUS values over time as percentage of total.

Thanks in advance!

Amila

[v-jiascu-msft]

Hi @amila,

Could you please mark the proper answers as solutions please?

Best Regards,

Dale

[Greg_Deckler · Accepted solution]

Should be something like:

Measure = DIVIDE(COUNTROWS(DISTINCT('Table3')),COUNTROWS(DISTINCT(ALL(Table3))),0)

The first COUNTROWS should be filtered according to the context. The second COUNTROWS removes all context and makes sure that you get all off the patients.