Unable to Recreate Seemingly Simple Visual

[Anonymous]

Hi,

I'm trying to recreate the below visual, but it seems impossible in Power BI, which is very hard to believe! I'm trying to stack Sales + Pipeline on one bar in a bar chart and compare it to another bar that is quota. This data is coming from three different tables, which are all related by employee name and date. Everything i've tried has either split it into 3 bars or stacked them all on top of each other, not the exact image below like I'm looking for. I even unioned all three tables together and used the legend and still didn't work. Any ideas would be greatly appreciated! 

[Greg_Deckler]

Very difficult to say given the current information provided. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

But, looks like a stacked column chart. You might need a disconnected table for your axis, it is difficult to say (see above). Disconnected Table Trick: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...

[Anonymous]

Sorry, let me provide more information. See below for a link to a sample dashboard which replicates my data and a screenshot which reflects what my desired result is. 

Link to sample dashboard:

https://www.dropbox.com/s/yks8fvytsicu2s8/Sample%20for%20Pipeline%20%26%20Sales%20vs.%20Quota.pbix?d...

Image of desired result (I need pipeline to be stacked on top of sales, but remain a different color):

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can try to create a calculated table as axis to meet your requirement:

Calculated table:

Axis = 
FILTER (
    CROSSJOIN (
        DISTINCT ( 'Pipeline'[Fiscal Period] ),
        { "Sales&Pipeline", "Quota" },
        SELECTCOLUMNS ( { "Sales", "Pipeline", "Quota" }, "Legend", [Value] )
    ),
    [Value] = [Legend]
        || ( [Value] = "Sales&Pipeline"
        && [Legend] IN { "Sales", "Pipeline" } )
)

Measure(as value field):

Measure = SWITCH(SELECTEDVALUE('Axis'[Legend]),"Sales",SUM(Sales[Sales]),"Pipeline", SUM('Pipeline'[Pipeline]),"Quota",SUM(Quota[Quota]))

The Left chart is a Multiple Axis chart from appsource, it does not need a axis table, but we did not find an effective way to add gap between two bars.

By the way, PBIX file as attached.

Best regards,

[Anonymous]

This is incredible! Very clever workaround, thank you so much.