Formatting Negative and Positive values in a Stacked Baar Chart

[Rabi]

Hi Everyone, 

Please Help me with this,

I have visualized sales in a stacked bar chart, based on the target sales I have calculated Excess/Shortfall sales values. so negative value is shortfall and positive value is excess. I want to format the negative and positive values with different colors.

Shortfall and Excess is coming from same column so cant be given different colours from formatting pane. I also tried separating excess and shortfall sales into two different column, this way i can assign different colours, but when multiple dates and stores selected a single date has bot excess and shortfall sales which is not right.

Please help me with this.

Regards,

Rabi

[parry2k]

@Rabi I will let someone else work on it. Not super happy with the reply. Good luck!

[parry2k]

@Rabi You are making it hard on us, where are Target Sales, Actual Sales columns? Are these measures? It shouldn't be that tough to explain. At this point, I would recommend putting a pbix file with sample data and any calculation you are doing and sharing that pbix file. I'm not going to do the work of preparing the raw data for you.

[Rabi]

Sorry @parry2k , EVERYTHING IS A COLUMN

[Rabi]

Hi @parry2kk and Everyone Below is the sample data and Dax:

Dax for Excess/Shortfall= Target sales-Actual sales

Sales Shortfall = IF ([Shortfall/Excess]<0, [Shortfall/Excess], 0)

Sales Exces= IF ([Shortfall/Excess]>0, [Shortfall/Excess], 0)

[parry2k]

@CoreyP I wouldn't go that route for many reasons, instead will calculate measures for excess/shortfall and then use that in the visual and that's why I'm asking what the raw data looks like. One solution doesn't fit all until you understand the data structure and I don't like to provide the solution if I'm fully clear about the underline data.

The reason wouldn't add new columns and unpivot, it will be expensive, impact on performance, and consume more memory because calculated columns need to be stored in memory. If it is a large table it will take way too long to refresh and end of the day you still have to create the measures to visualize the data. Anyhow, that's my approach and thinking. 

[parry2k]

@CoreyP Unfortunaltey I don't think it is the right approach in this case since Excess and Shortfall are getting calculated. I could be completely wrong.

[CoreyP]

@parry2k , wouldn't the calculated values in the applied steps prior to the unpivot remain the same after the unpivot? I mean the crux of the issue is that you cannot conditionally format with two measures; it needs the legend field populated. What if he creates a legend dimension, almost like the table a field paramter creates?

[CoreyP]

What if you were to then unpivot the table so it would be in the format of:

Date | Type | Amount
27/08/2023 | Actual Sales | $x
27/08/2023 | Target Sales | $x
27/08/2023 | Excess Sales | $x
27/08/2023 | Shortfall Sales | $x

That way you just have one measure, SUM( [Amount] ), and you can use the Type field in the Legend.

[parry2k]

@Rabi you can always generate a sample data and share it. 

[parry2k]

@Rabi can you share how your sample data looks like and what measures you are using to calculate short/gain?

[Rabi]

Hi @parry2k , 

To calculate the excess and shortfall, I first Subtracted Actual sales with target. Then I separated values >0 into a new column Excess and i separated values <0 in a new column shortfall. unfortunately i wount be able to share the data as it is very sensitive.