Stacked Bar Chart with Groupings

Hi littlemojopuppy,

Since you have used the direct query mode to connect the database, it is hard to achieve your requirement by dax function because of there are some limitations which you use measure or calculate column on direct mode.

You can use query editor to work through the issue:

Open the "Query editor" and click on "Advanced Editor", then add a custom step.
avg=List.Average(Table.SelectRows(Table.SelectColumns(PerviousStep,"ColumnName"),each _<> null)[ColumnName])

Add a custom column and use above variable to calculate the result.

#"Added Custom" = Table.AddColumn(PerviousStep, "State", each if [ColumnName]>avg then "Over" else if [ColumnName]=avg then "Equal" else "Less")

Screenshots:

Base Table.

 Target variable.

Add custom column to check the state.

Save and return to report view, create a visual with the new column as the axis field.

In addition, you can drag the original column to legend field to show the detail information.

Full query:

let
    Source = Sql.Databases("xxxxxx"),
    qiuyun = Source{[Name="xxxxx"]}[Data],
    dbo_Table_1 = qiuyun{[Schema="dbo",Item="Table_1"]}[Data],
    avg=List.Average(Table.SelectRows(Table.SelectColumns(dbo_Table_1,"amount"),each _<> null)[amount]),//get the average
    #"Added Custom" = Table.AddColumn(dbo_Table_1, "State", each if [amount]>avg then "Over" else if [amount]=avg then "Equal" else "Less")
in
    #"Added Custom"

Regards,

Xiaoxin Sheng