creating a positive and negative bar chart on one axis

[ViralPatel212]

Hello Guys,

I am trying to recreate the following image on powerbi: 

I was able to recreate abot 90% of it onto powerbi using stacked column chart however it looks like its totalling wrong. 

The graph i have create has got:

1) Dates hierarchy - Year, Month and WeekNumber in the X-axis

2) Volume Variance measure in Y-axis

3) Counter Party  column in Legend

Measure / calcualted Column	Formula
Volume Variance	Volume Variance = VAR _buyValue = CALCULATE(     SUM('glimpse-prod-db output_file_dtl'[Buy side value]),     'glimpse-prod-db output_file_dtl'[side] = "Buy" ) VAR _sellValue = CALCULATE(     SUM('glimpse-prod-db output_file_dtl'[Buy side value]),     'glimpse-prod-db output_file_dtl'[side] = "Sell" ) VAR _totaslValue = SUM('glimpse-prod-db output_file_dtl'[Buy side value]) RETURN IF(     _totaslValue > 0,     _buyValue,     _sellValue )
Buy side Value (Calculated Column)	Buy side value = IF('glimpse-prod-db output_file_dtl'[side] = "Buy", 'glimpse-prod-db output_file_dtl'[size_in_eur], IF('glimpse-prod-db output_file_dtl'[side] = "Buy", ('glimpse-prod-db output_file_dtl'[size_in_eur] * -1))))

As example - when i was looking at Citi (counterparty) for the 15th of April 24 it should show me both a buy (stacked column being postive) and a sell (stacked column being negative) in the visual. However it is only reflecting the sell side or it will show the total as shown below:

 I would want it to show both bars for the 15th showing -150 and 0.55?

Is this possible as i can't add 2 y Axis as i am using a legend

Thanks

viral

[Anonymous]

Hi @ViralPatel212 

Can you tell me if your problem is solved? If yes, please accept it as solution.

Regards,

Nono Chen

[ViralPatel212]

hi @Anonymous 

thanks for the attempt. 

For some reason that does not work for me. 

Here you can find a sample file : Sample File 

The desired outcome is to have the counterparty in the legend but then showing the postive bar and the negative bar for the 15th. at the moment it is giving the total for the day which is incorrect.

[Anonymous]

Hi @ViralPatel212 

I think it has to do with the logic of your code.

IF(
    _totaslValue > 0,
    _buyValue,
    _sellValue
)

If _totaslValue is greater than 0 in your data, only _buyValus will be returned.

I changed your code.

Volume Variance = 
var _buyValue = 
CALCULATE(
    SUM('Table'[Buy side value]),
    'Table'[side] = "Buy"
)
var _sellValue = 
CALCULATE(
    SUM('Table'[Buy side value]),
    'Table'[side] = "Sell"
)
RETURN 
IF(
    SELECTEDVALUE('Table'[side]) = "Buy",
    _buyValue,
    IF(
        SELECTEDVALUE('Table'[side]) = "Sell",
        _sellValue
    )
)

Here is the result.

Regards,

Nono Chen

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

[ViralPatel212]

hi @Anonymous 

Thanks for that. THat works if i have the side in the legend, however if I change side legend to counter_party column i am not getting any result? My main objective is to show the negative and postive values broken down by counter_party.

Negative being my Sell values

Postive being my Buy values 

thanks

[Anonymous]

Hi @ViralPatel212 

As far as the data you provide is concerned, positive and negative values are displayed for "side" and have nothing to do with "counter_party".

Therefore, you cannot display negative and postive values using counter_party.

[Anonymous]

Hi @ViralPatel212

Select Create Stacked column chart.

Here is the result.

If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

Regards,

Nono Chen

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