COuntRows of multiple columns for Stacked Bar Chart

[AMBP1973]

Hi, I would like to create a horizontal stacked bar chart with each bar showing count of values from a seperate column. Seeking advice on how to go about this and what is the appropropriate dax measure.

Thanks in advance

[123abc]

Create a New Table:

• You might want to create a new table that consolidates the values from different columns into a single column. This can be done using the UNION function or other appropriate functions based on your data structure.

NewTable = UNION(VALUES(Table[Column1]), VALUES(Table[Column2]), VALUES(Table[Column3]))

1. Replace Table with your actual table name, and Column1, Column2, Column3 with your actual column names.

2. Count Rows in the New Table:

   • Create a measure to count the rows in the new table.

CountRowsMeasure = COUNTROWS(NewTable)

1. Create a Stacked Bar Chart:

   • Use the new measure (CountRowsMeasure) as the Values field in your horizontal stacked bar chart.
   • Use the original columns (Column1, Column2, Column3) as the Axis or Legend fields to stack the bars based on those columns.

   This way, each bar in your chart will represent the count of values from a separate column.

Here's an example of what the DAX might look like:

NewTable = UNION(VALUES(Table[Column1]), VALUES(Table[Column2]), VALUES(Table[Column3]))

CountRowsMeasure = COUNTROWS(NewTable)

Adjust the DAX expressions based on your specific data model and requirements.

Remember, this is a generalized approach, and the exact steps might vary depending on your specific dataset and the tool you are using (Power BI, Excel, etc.). If you have more specific details about your data structure or the tool you are using, I can provide more targeted guidance.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[AMBP1973]

@123abc 

Thankyou for providing this solution. I have completed your instructions however it is not returning the results in the visual for each Column, nor the correct count. Below is what I have applied and the results. Would appreciate any further advice on this.

Table

Actions Table = UNION(VALUES('E&T Documented Information Review Tracker'[SRImprovementAction]),VALUES('E&T Documented Information Review Tracker'[SLImprovementAction]),VALUES('E&T Documented Information Review Tracker'[PEImprovementAction]),VALUES('E&T Documented Information Review Tracker'[IR1ImprovementAction]),VALUES('E&T Documented Information Review Tracker'[IR2ImprovementAction]),VALUES('E&T Documented Information Review Tracker'[IR3ImprovementAction]),VALUES('E&T Documented Information Review Tracker'[IR4ImprovementAction]))

Count Measure

Count Actions = COUNTROWS('Actions Table')

Visual

There should actually be a count of 57 for 'Readiness Assessment', 1 for 'Peer Review' and remainder 'Blank'.

[123abc]

It appears that the UNION function may not be the most suitable approach for your case, as it combines distinct values from different columns into a single column. Since you want to count the occurrences of specific values in each column separately, a different approach is needed.

You can use the following steps to achieve your goal:

1. Create a New Table: Instead of using UNION, create a new table that contains the values from each column separately.

Actions Table =
UNION(
SELECTCOLUMNS('E&T Documented Information Review Tracker', "Action", 'E&T Documented Information Review Tracker'[SRImprovementAction]),
SELECTCOLUMNS('E&T Documented Information Review Tracker', "Action", 'E&T Documented Information Review Tracker'[SLImprovementAction]),
SELECTCOLUMNS('E&T Documented Information Review Tracker', "Action", 'E&T Documented Information Review Tracker'[PEImprovementAction]),
SELECTCOLUMNS('E&T Documented Information Review Tracker', "Action", 'E&T Documented Information Review Tracker'[IR1ImprovementAction]),
SELECTCOLUMNS('E&T Documented Information Review Tracker', "Action", 'E&T Documented Information Review Tracker'[IR2ImprovementAction]),
SELECTCOLUMNS('E&T Documented Information Review Tracker', "Action", 'E&T Documented Information Review Tracker'[IR3ImprovementAction]),
SELECTCOLUMNS('E&T Documented Information Review Tracker', "Action", 'E&T Documented Information Review Tracker'[IR4ImprovementAction])
)

1. This way, you have a table with a column named "Action" that contains values from each of your original columns.

2. Create Count Measures: Create separate count measures for each action.

CountSRImprovementAction = COUNTROWS(FILTER('Actions Table', 'Actions Table'[Action] = "SRImprovementAction"))
CountSLImprovementAction = COUNTROWS(FILTER('Actions Table', 'Actions Table'[Action] = "SLImprovementAction"))
CountPEImprovementAction = COUNTROWS(FILTER('Actions Table', 'Actions Table'[Action] = "PEImprovementAction"))
CountIR1ImprovementAction = COUNTROWS(FILTER('Actions Table', 'Actions Table'[Action] = "IR1ImprovementAction"))
CountIR2ImprovementAction = COUNTROWS(FILTER('Actions Table', 'Actions Table'[Action] = "IR2ImprovementAction"))
CountIR3ImprovementAction = COUNTROWS(FILTER('Actions Table', 'Actions Table'[Action] = "IR3ImprovementAction"))
CountIR4ImprovementAction = COUNTROWS(FILTER('Actions Table', 'Actions Table'[Action] = "IR4ImprovementAction"))

1. Adjust these measures according to your actual action names.

2. Create Stacked Bar Chart: Use these measures in a horizontal stacked bar chart.

   • Drag the measures to the Values field of the visual.
   • Drag the common dimension (e.g., a categorical column) to the Axis field.

Now, your visual should display the counts of each action separately. If there are still issues, please double-check the action names and the data in your columns to ensure they match.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.