Calculate total % in matrix visual

[itsmeanuj]

Hi Guys,

This is what my data looks like:

Month	patient Age	Patient ID
1-Jan	<=30	A1
1-Feb	>30	A2
1-Mar	<=30	A3
1-Jan	<=30	A4
1-Mar	31-60	A5
1-Jan	<=30	A6
1-Feb	>30	A7
1-Mar	31-60	A8
1-Jan	<=30	A9
1-Mar	31-60	A10
1-Jan	<=30	A11
1-Feb	>30	A12
1-Mar	31-60	A13
1-Mar	>30	A14
1-Feb	31-60	A15

I have created measures to show the total count of patients in each month in each category and their % contribution in each month.

Dummy Total = CALCULATE(DISTINCTCOUNT('Dummy Data'[Patient ID]))

Dummy Total% = DIVIDE(CALCULATE(count('Dummy Data'[Patient ID])),CALCULATE(COUNT('Dummy Data'[Patient ID]),ALLSELECTED()))

I have created a matric visual which looks like this

 

Currently, the % is being calculated considering the denominator as 15 (count of patients across all the months), however, I need to show the denominator based on the total patients in that particular month. For Mar '23 (highlighted), the denominator should be 6 and not 15 while calculating %. Can someone please look into the Dummy total calculation and help me get the desired results? I am still learning DAX and need help.

[HarishKM]

@itsmeanuj Hey,
you can use this .

output based on months

Thanks

Harish M

[johnyip]

@itsmeanuj , please try the following

Dummy Total% = 
DIVIDE(CALCULATE(count('Dummy Data'[Patient ID])),CALCULATE(COUNT('Dummy Data'[Patient ID]),REMOVEFILTERS('Dummy Data'[patient Age])))

[itsmeanuj]

@johnyip - Thank you so much. Seems like it is working but I will test it more. Also i want to show the values within parenthesis in my visual. Like (27%). Do you know how we can concatenate that?

[johnyip]

There are two ways.

First is to create a new measure (or modify the existing one to the equivalent) as follows.

Dummy Total% (Reporting) = 
VAR Str = FORMAT([Dummy Total%]*100,"#.##")
RETURN
"(" & IF(FIND(".",Str)=LEN(Str),FORMAT([Dummy Total%]*100,"#"),Str) & "%)"

Second is to modify the display format of your existing measure to include brackets at the beginning and the end.

[itsmeanuj]

@johnyip - tried this but all the values go blank once I add this new column in values.

[johnyip]

Is the sample data you provided having the same structure as your real data?