How to solved error when I am using percentages in a matrix

[sebastianslzr]

I have the following data source where I have to count by visit date the times that a process has passed through each month in each state, applying the visit date as the main filter for the following example I do it with the month of April.

The following is the data source I use.

City	Location	Name	Visit Date	Status	Enroll Month	Enroll Year
Salt Lake	Capitol Building	Rhoda Horton	27-05-23	Open	May	2023
Salt Lake	Capitol Building	Rhoda Horton	27-05-23	Progress	May	2023
Salt Lake	Capitol Building	Rhoda Horton	27-05-23	Canceled	May	2023
Salt Lake	Capitol Building	Rhoda Horton	27-05-23	Closed	May	2023
Salt Lake	Capitol Building	Barry Parrish	28-04-23	Open	April	2023
Salt Lake	Capitol Building	Barry Parrish	28-04-23	Progress	April	2023
Salt Lake	Capitol Building	Barry Parrish	28-04-23	Canceled	April	2023
Salt Lake	Capitol Building	Barry Parrish	28-04-23	Closed	May	2023
Salt Lake	Red Butte Garden	Carolyn Hogan	25-04-23	Open	April	2023
Salt Lake	Red Butte Garden	Carolyn Hogan	25-04-23	Progress	May	2023
Salt Lake	Red Butte Garden	Carolyn Hogan	25-04-23	Canceled	May	2023
Salt Lake	Red Butte Garden	Carolyn Hogan	25-04-23	Closed	June	2023
Salt Lake	Capitol Building	Farrah Gray	23-04-23	Open	April	2023
Salt Lake	Capitol Building	Farrah Gray	23-04-23	Progress	August	2023
Salt Lake	Capitol Building	Farrah Gray	23-04-23	Canceled	August	2023
Salt Lake	Capitol Building	Farrah Gray	23-04-23	Closed	August	2023

To count the number of times a process has been in each month based on the date of visit, I use the following measure:

Total Proc. Open = COUNTROWS(FILTER(Table1,Table1[STATUS] = "Open")

The same goes for each of the different Status:

Total Proc. Progress = COUNTROWS(FILTER(Table1,Table1[STATUS] = "Progress")) 
Total Proc. Canceled = COUNTROWS(FILTER(Table1,Table1[STATUS] = "Canceled")) 
Total Proc. Closed = COUNTROWS(FILTER(Table1,Table1[STATUS] = "Closed"))

To make all this data fit the matrix I'm making, I use an additional measure where I make use of a Switch:

Measure Status =
    SWITCH(VALUES(Table1[STATUS]),
    "Open",[Total Proc. Open],
    "Progress",[Total Proc. Progress],
    "Canceled",[Total Proc. Canceled],
    "Closed",[Total Proc. Closed])

So far everything is working correctly for me and the data is displayed correctly as I want in the following matrix:

The error happens when I want to show the percentages of the previous data in a new matrix, i.e. I want them to be displayed as follows:

For the calculation of the percentages in the matrix, I have done something similar to the previous matrix where I use a Switch, the measure is as follows:

Percentage Measure = 
SWITCH(VALUES(Table1[STATUS]), 
"Open","100%",
"Progress",DIVIDE(CALCULATE(COUNTROWS(Table1),Table1[STATUS] = "Progress"),CALCULATE(COUNTROWS(Table1),Table1[STATUS] = "Open")),
"Canceled",DIVIDE(CALCULATE(COUNTROWS(Table1),Table1[STATUS] = "Canceled"),CALCULATE(COUNTROWS(Table1),Table1[STATUS] = "Open")),
"Closed",DIVIDE(CALCULATE(COUNTROWS(Table1),Table1[STATUS] = "Closed"),CALCULATE(COUNTROWS(Table1),Table1[STATUS] = "Open")))

When using the previous measure it only shows me the percentages of the month that I have selected in the filter and does not show me the others, an example below of what is shown is the following:

Are there any changes I need to make to my measure to achieve the goal?

[v-kaiyue-msft · Accepted solution]

Hi @sebastianslzr ,

The unexpected result you obtained is because there is a problem with counting the status of open, as shown in the figure below. At this time, only April has open data, so only the data of April will be displayed in the matrix.

You can use the ALLSELECTED function to change the context and obtain the desired results.

Measure =
VAR _count1 = CALCULATE(COUNTROWS('Table1'))
VAR _count2 = CALCULATE(COUNTROWS('Table1'),FILTER(ALLSELECTED('Table1'),'Table1'[Status] = "Open"))
RETURN
DIVIDE(_count1,_count2)

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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