Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'm looking for assistance with a clustered bar chart. I am struggling to create a measure that gives me a % of a category([Project Type] - added as Legend), not % of grand total.
I have a number of slicers on my report; so would need the % of the category ([Project Type]) to adjust if the slicer modifies the filter. I tried creating a measure for this (but this seems to just replicate the % of GT):
Turnaround_%_of_projects = COUNT(Project_Tracking[Weeks to Turnaround]) / CALCULATE( COUNT( Project_Tracking[Weeks to Turnaround]), ALLSELECTED(Project_Tracking))
When I tried using ALLEXCEPT - and including all of the fields from my slicers, my denominator ends up being huge for some reason. If I tried using KEEPFILTERS with ALLEXCEPT, then the denominator does not seem to stay the same per category...
Turnaround_%_of_projects = COUNT(Project_Tracking[Project Type]) / CALCULATE( COUNT( Project_Tracking[Project Type]),ALLSELECTED (Project_Tracking), KEEPFILTERS( ALLEXCEPT( Project_Tracking,Project_Tracking[Project Type])))
In visual terms, expect the each category (Legend item) to add up to 100% (e.g. the dark bars below add to 48%)
I would appreciate any assistance / insight anyone has on this.
My data looks like the below:
I essentially need it to show the below for the clustered chart to show the correct %s:
Solved! Go to Solution.
You may use the following DAX to create a new table.
Table = ADDCOLUMNS ( SUMMARIZE ( Project_Tracking, Project_Tracking[Project Type], Project_Tracking[Weeks to Turnaround], "Count of Projects", COUNTROWS ( Project_Tracking ) ), "% of Project Type", [Count of Projects] / COUNTROWS ( FILTER ( Project_Tracking, Project_Tracking[Project Type] = EARLIER ( Project_Tracking[Project Type] ) ) ) )
You may use the following DAX to create a new table.
Table = ADDCOLUMNS ( SUMMARIZE ( Project_Tracking, Project_Tracking[Project Type], Project_Tracking[Weeks to Turnaround], "Count of Projects", COUNTROWS ( Project_Tracking ) ), "% of Project Type", [Count of Projects] / COUNTROWS ( FILTER ( Project_Tracking, Project_Tracking[Project Type] = EARLIER ( Project_Tracking[Project Type] ) ) ) )
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |