100% Stacked and Clustered Bar Chart with dynamically filtered data

[Anonymous]

Hello,

I'm relatively new to Power B.I. and SQL but am quite advanced at graph creation using R.

MY END GOAL: To create a 100% Stacked and Clustered Bar Chart. I reasearched how to create this but it seems that it is not possible and there is no custom visualization that fits this chart type (please correct me if otherwise). A chart where the bars for comparison are on top of each other would work as well.

MY ISSUE: I have two filtered columns: Driver_name and timeBand. I would like to compare any particular driver's distribution of performance (ontime, early, late) to the average (including or excluding the driver selected) within the routes he/she drives. I've thought of at least two ways to tackle these problems, but neither of them are working for me:

Solution Attempt A) Create a dynamic column that can be used within a hierarchy

1. Create a Measure called Average_Logic that contains the text "Driver" if it is the driver or "Average" as an exclusive average. 

Avg_logic =

IF(SELECTEDVALUE(SwiftlyJoinedWithTrapeze_20200129[timeBand])==TimeBandTable[timeBand_selection], "Driver", "Average")

2. Add measure to axis and select "Expand all down one level in the hierarchy". This is where it breaks for me. All the solutions I read on forums recommended creating a measure rather than a column but I don't believe one can add measures to an axis... If one could, this is what I would want it to do. 

Solution Attempt B) Create merged calculated tables

1. Create a calculated table that applies both the Driver_name and timeBand filter from the slicer

2. Create a calculated table that applies only the timeBand filter, and changes the [route_short_name] column to be its concatenatenation with the text " Average"

3. Create a merged table between the two above and link the visualization to this merged table

I am having a hard time with Solution Attempt B, as I try to understand how to filter and unfilter tables and merge them together. Any tips regarding these and other potential solutions would be appreciated.

PHOTO: I want to combine these two to be on the same graph because comparing distributions on a horizontal bar chart is easier done vertically than horizontally. 

[v-alq-msft]

Hi, @Anonymous 

Based on my research, It is unavailable to  create a dynamic column and use a measure as an axis. Even if they look similar, there is a big difference between calculated columns and measures. The value of a calculated column is computed during data refresh and uses the current row as a context; it does not depend on user activity on the pivot table. A measure operates on aggregations of data defined by the current context. In a pivot table, for example, source tables are filtered according to the coordinates of cells, and data is aggregated and calculated using these filters. In other words, a measure always operates on aggregations of data under the evaluation context.

About how to filter and unfilter tables, you may refer to the following link.

https://docs.microsoft.com/en-us/power-bi/power-bi-report-filter-types

Best Regards

Allan

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