Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
littlemojopuppy
Community Champion
Community Champion

Stacked Bar Chart with Groupings

Hi!  I'm hoping this is a really easy question that comes down to operator error due to relative unfamiliarity with PowerBI.  Any help would certainly be appreciated.  I have a bar chart that's showing the customer count by region.Capture.JPG

I have some performance guardrails that are included as measures...for example a customer was less than minimum, less than target or over target.  I want to have each of these three shown as groups on this chart.

 

What I've done: I've created measures to calculate the customer's performance metric and then a second measure for grouping based on the first metric.  I try to add that grouping measure to the Legend and nothing happens.  Could anyone please help?

1 ACCEPTED SOLUTION

Good morning!

 

So if the thing creating the problem was using Direct Query mode I changed the data connection to Import instead.  There was no reason it had to be Direct Query other than I'm a novice.  After changing the data connection I went back to creating a column for the grouping.  And it worked!

Capture.JPG

Thank you for your help!

View solution in original post

6 REPLIES 6
littlemojopuppy
Community Champion
Community Champion

One thing I want to add...if I add a field defined in the data (not a calculated measure) such as sales person or area, it works.  It doesn't seem to like the calculated measure being what it's supposed to group by.

Hi @littlemojopuppy,

 

For your requirement, you can use calculate column to calculate the target, then drag it to axis field.

 

For example:

 

TargetColumn =
var target= AVERAGEX(ALL(Sheet2),[Amount])*0.75
Return
if([Amount]>target,"Over target","Less than target")

 

Capture.PNG

 

In addition, you can drag your original axis column to legend field to show the detail info.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Apologies if this reply is duplicated...my original response doesn't seem to be here.

 

I set up two measures for the minimum and target (but I like how you're calculating the 75% of average interactively!)  The formula to determine which grouping a record should be in is as follows: Net TGP Per Case Grouping = IF([Net TGP Per Case]<[Net TGP Per Case Minimum],"Below Minimum",IF(AND([Net TGP Per Case]>=[Net TGP Per Case Minimum],[Net TGP Per Case]<[Net TGP Per Case Target]),"Below Target","Above Target")).

 

The requirement is to have geography shown on the axis since the rest of the dashboard will be based on geography.  What I'm looking for is what's shown as a solid green bar to be broken into three segments, one for each of the groupings below min, below target and above target.  Here's an Excel mock-up of what I'm trying to get to.

Capture.JPG

Thank you for any help you might be able to provide!

 

 

Hello again!  I noticed that you suggested a column as opposed to a measure.  I tried creating a column in place of the measure...I got an error saying that "Function 'SUM' is not allowed as part of calculated column DAX expressions on DirectQuery models".  I'd bet that this is due to using the measure (which is based on an aggregation of underlying data) in defining the column.

 

So I can't change the measure used in the comparison to determine grouping because it's used elsewhere.  But if I were to create a column with the same formula as the measure and then a second column with the grouping that should work?  Maybe?

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:

Capture.PNG

Base Table.

Capture2.PNG

 Target variable.

Capture3.PNG

Add custom column to check the state.

 

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

Capture4.PNG

 

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

Capture5.PNG

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Good morning!

 

So if the thing creating the problem was using Direct Query mode I changed the data connection to Import instead.  There was no reason it had to be Direct Query other than I'm a novice.  After changing the data connection I went back to creating a column for the grouping.  And it worked!

Capture.JPG

Thank you for your help!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.