Forum Discussion
Row Counts & Percentages Within Percentages While Applying Filters
Hello, I'm really hoping one of you DAX wizards can help me out. Below is a fabricated dataset (it's for work, but the layout looks like this).
| ID | Status | Response | Program | Date |
| 001 | Open | Accept | A | 1/1/2000 |
| 002 | Open | Accept | A | 1/1/2000 |
| 003 | Closed | Reject | B | 1/1/2000 |
| 004 | Open | Reject | C | 1/1/2000 |
| 005 | Open | Accept | D | 1/1/2000 |
| 001 | Open | Accept | A | 2/1/2000 |
| 002 | Open | Accept | A | 2/1/2000 |
| 003 | Closed | Reject | B | 2/1/2000 |
| 004 | Closed | Reject | C | 2/1/2000 |
| 005 | Open | Accept | D | 2/1/2000 |
What I have been able to create:
- A measure which calculates the percentage of unique ID's which were both open and accepted by each date (e.g. the percentage of unique ID's which were both open and accepted for 1/1/2000 is 75%. For 2/1/2000 it is 100%).
What I'd like:
- A measure calculating percentage within percentage by program (e.g. of the 100% of IDs which were accepted and open for 2/1/2000, 2 belong to program A and 1 belongs to program D, so the percentages would be 0.66 and 0.33, respectively).
- It needs to be used in a stacked barchart (if possible). What the end result should be is a measure which, when used in a stacked bar plot, will display the overall percentage of ID's which were both open and accepted (this needs to be the height of the barchart, as that's a major requirement...a 100% stacked barchart is a no-go from mgmt) by date, as well as relative percentages by program within each of those bars. The first plot in this link is what the end result should look like: https://blog.coupler.io/power-bi-stacked-bar-chart/#How_to_show_percentage_in_a_stacked_bar_chart_in_Power_BI
Having to pre-filter by looking for open and accepted values seems to be making this harder (though it could also be a misunderstanding of DAX in general).
I appreciate the help!
Hi hillb33,
Thank you for reaching out to the Microsoft fabric community forum. I reproduced the scenario, and it worked on my end. I used it as sample data and successfully implemented it.
outcome:
I am also including .pbix file for your better understanding, please have a look into it.Hope this clears it up. Let us know if you have any doubts regarding this. We will be happy to help.
Thank you for using the Microsoft Fabric Community Forum.
4 Replies
- Ashish_Mathur
Super User
Hi,
Based on the input table that you have shared, show the expected result in a simple Table format. From there, we can build the visual we want.
- v-kpoloju-msft
Community Support
Hi hillb33,
Thank you for reaching out to the Microsoft fabric community forum. I reproduced the scenario, and it worked on my end. I used it as sample data and successfully implemented it.
outcome:
I am also including .pbix file for your better understanding, please have a look into it.Hope this clears it up. Let us know if you have any doubts regarding this. We will be happy to help.
Thank you for using the Microsoft Fabric Community Forum.
- v-kpoloju-msft
Community Support
Hi hillb33,
Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.Thank you.
- v-kpoloju-msft
Community Support
Hi hillb33.,
Just wanted to follow up. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.Thank you.