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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MrJLa
New Member

Multiple measure-based columns in one stacked bar chart

I'm trying to add two stacked columns to one stacked column chart and can't figure out how to do it.

I've created four measures.  Two of them--call them P1 and P2--are prices derived from a table imported from an Excel worksheet.  The other two--D1 and D2--are the same prices with some discounts applied.  The discounts come from a set of slicers whose values are a set of constants: 0, 5, 10, 15, and so on.  The idea is to let the user set the discounts and see how the discounted D1-D2 stack compares to the original prices in the P1-P2 stack.

I can easily create a stacked column chart that uses the P1 and P2 measures as values, and another stacked column chart that uses the D1 and D2 measures as values.  That gives me two charts that look like this:

Original Prices
P1
P2

Discounted Prices
D1
D2

But these two charts can have different y-axis ranges, and that makes the stacks hard to compare.  What I'd like to have is one chart that contains both stacks:

Original and Discounted Prices
P1  D1
P2  D2

How can I do that?  I've tried using ROW and UNION to create a table that contains the four measures, and feeding that table to one stacked column chart, but the table (and therefore the chart) don't seem to get updated when the user changes the discount slicers.

 

Thanks!

2 REPLIES 2
v-qiuyu-msft
Community Support
Community Support

Hi @MrJLa,

 

Would you please share some sample data and screenshots about expected results for our analysis?

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I'd be happy to.  Here is a simplified example.

 

Let's say I have a "Prices" table that contains original and discounted prices for two products:

 

Prices tablePrices table

 

I can easily use a stacked column chart to show the revenue that these prices would generate, like so:

 

Stacked column chart that I wantStacked column chart that I want

 

This stacked column chart is exactly what I want.

 

The problem, however, is that the Prices table I showed above doesn't actually contain a "With Discount" row.  Instead, the user dynamically selects a discount from a slicer:

 

Discount slicerDiscount slicer

...and then I use two measures to calculate the discounted prices using the selected discount:

 

  • Product1WithDiscount = ( 1 - ( LASTNONBLANK ( 'Discounts'[Discount], 0 ) ) / 100 ) *  LASTNONBLANK( Prices[Product 1], 0 )
  • Product2WithDiscount = ( 1 - ( LASTNONBLANK ( 'Discounts'[Discount], 0 ) ) / 100 ) *  LASTNONBLANK( Prices[Product 2], 0 )

 

When the user selects a new discount, the height of the "With Discount" column should change.

 

So now I have four values that I want to use to create the stacked column chart I showed above--two original prices that are pulled from a table, and two discounted prices that are calculated dynamically using measures.

 

Is there any way to do this?

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.