I am using a stacked bar chart to display the number of projects in each status color by VP. I need to count the unique project codes when the ALL slicer is selectd for the project month. The table feeding this visualization has a line for each month a project update report was submitted, so each project has multiple lines and I need a count of the unique project codes. I appreciate your assistance.
| Project Code | OP | Project Name | Start | Finish | Forecast Delivery Date | Sponsor | Report Status | Status Report Date |
| PRJ01 | OP01 | OP01 project 1 | 2022-03-01 | 2024-10-31 | 2024-10-31 | VP Name | Final | 2024-01-23 |
| PRJ01 | OP01 | OP01 project 1 | 2022-03-01 | 2024-12-06 | 2024-12-06 | VP Name | Final | 2024-02-20 |
| PRJ01 | OP01 | OP01 project 1 | 2022-03-01 | 2025-07-09 | 2025-07-09 | VP Name | Final | 2024-03-19 |
| PRJ01 | OP01 | OP01 project 1 | 2022-03-01 | 2025-07-09 | 2025-07-09 | VP Name | Final | 2024-04-15 |
| PRJ02 | OP02 | OP02 project 2 | 2024-05-01 | 2024-12-06 | 2024-12-06 | VP Name | Final | 2024-01-23 |
| PRJ02 | OP02 | OP02 project 2 | 2024-05-01 | 2024-12-06 | 2024-12-06 | VP Name | Final | 2024-02-20 |
| PRJ02 | OP02 | OP02 project 2 | 2024-05-01 | 2024-12-06 | 2024-12-06 | VP Name | Final | 2024-03-19 |
| PRJ02 | OP02 | OP02 project 2 | 2024-05-01 | 2024-12-06 | 2024-12-06 | VP Name | Final | 2024-04-15 |
This is the dashboard. The project count of 31 is correct as is the not green, which is counting only one time that the project went not green, not every month. As you can see the stacked bar chart's count is not consistent with those numbers.
This is the filter and visualization for the stacked bar chart. I don't see an option for a value field.
This is the measure that I am using for the count of projects that returns the correct count of 31.
Count of Projects = DISTINCTCOUNT(Projects[Project Code]) +0
I use the same measure for the Not Green count that returns the correct count of 19 with an additional filter on the status color to return only red or amber.
This is the updated table with the status values added. 10=green, 20=amber, 30=red. The stacked bar chart should only count a project not green once even if it is not green for multiple months.
| Project Code | OP | Project Name | Start | Finish | Forecast Delivery Date | Sponsor | Report Status | Status Report Date | Overall Status |
| PRJ01 | OP01 | OP01 project 1 | 2022-03-01 | 2024-10-31 | 2024-10-31 | VP Name | Final | 2024-01-23 | 20 |
| PRJ01 | OP01 | OP01 project 1 | 2022-03-01 | 2024-12-06 | 2024-12-06 | VP Name | Final | 2024-02-20 | 30 |
| PRJ01 | OP01 | OP01 project 1 | 2022-03-01 | 2025-07-09 | 2025-07-09 | VP Name | Final | 2024-03-19 | 30 |
| PRJ01 | OP01 | OP01 project 1 | 2022-03-01 | 2025-07-09 | 2025-07-09 | VP Name | Final | 2024-04-15 | 30 |
| PRJ02 | OP02 | OP02 project 2 | 2024-05-01 | 2024-12-06 | 2024-12-06 | VP Name | Final | 2024-01-23 | 10 |
| PRJ02 | OP02 | OP02 project 2 | 2024-05-01 | 2024-12-06 | 2024-12-06 | VP Name | Final | 2024-02-20 | 20 |
| PRJ02 | OP02 | OP02 project 2 | 2024-05-01 | 2024-12-06 | 2024-12-06 | VP Name | Final | 2024-03-19 | 20 |
| PRJ02 | OP02 | OP02 project 2 | 2024-05-01 | 2024-12-06 | 2024-12-06 | VP Name | Final | 2024-04-15 | 20 |
The result I need is for the total project count of the stacked bar chart to match the Projects count of 31 and the Not Green count of 19, a combination of the red and amber projects, when all months are selected in the slicer. If I select an individual month I get a correct count, it is only when selecting all months that it is not counting unique projects and status.
This is an example of a single month and all the counts match up and are correct.
Hi @Jhadur ,
According to your statement, I am confused that what is the X axis. Is it [Project Code]? I see that you select 2024 January in your slicer, I think it should only show one status for each Project Code.
I create a sample by your data.
Please share a sample file with us.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code FABINSIDER for a $400 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.
Fabric Community Update - March 2025
Find out what's new and trending in the Fabric community.
