Forum Discussion
Community ChampionStacked Bar Chart with Groupings
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!
Thank you for your help!
Community ChampionOne 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")In addition, you can drag your original axis column to legend field to show the detail info.
Regards,
Xiaoxin Sheng
- littlemojopuppy
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.
Thank you for any help you might be able to provide!
- littlemojopuppy
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?