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:
enter image description here
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:
enter image description here
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:
enter image description here
Are there any changes I need to make to my measure to achieve the goal?
John Doe is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.