cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Compare Selected quarter on parallel bar chart

I want to compare total balance of 2 selected quarter for different categories in a parallel bar chart format. To achieve this I've created 2 different columns and created 2 measures corresponding to each quarter column like: 

 

Measure-1 = CALCULATE(SUM(Sheet1[Balance]),FILTER(Sheet1, Sheet1[Quarter1]=SELECTEDVALUE(Sheet1[Quarter1])))
Measure-2 = CALCULATE(SUM(Sheet1[Balance]),FILTER(Sheet1, Sheet1[Quarter1]=SELECTEDVALUE(Sheet1[Quarter2])))

The measure values are getting populated, but when I try to plot these measures on clustered column chart, nothing shows up. It looks like both the selected values are interacting with the chart hence nothing gets plotted. Is there any workaround for this?

 

So the idea is when I select the quarters from 2 different slicers, the corresponding measures gets calculated and I can plot the sum of balance on parallel bar plot for different categories on x-axis. I'm attaching the pbix file for reference. 

 

https://drive.google.com/file/d/1QdbAit2-CjJ0naa3xOxKW6T_K0XQMbRa/view?usp=sharing 

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Helper II
Helper II

@parag123 

Hi parag,

If you make 2 date-slicers which exists in the same table, it would slice each other ending up having null table.

the solution would be introducing two calculate tables which can be used as slicers.

 

From your data,

Step1. Create calculated quater column in the main table (as you made for [quater1] and [quater2]):

Quarter = CONCATENATE(FORMAT([Date],"YYYY"), CONCATENATE(" Q",ROUNDUP(MONTH([Date])/3,0)))

 

Step2. Create two tables (Sellecting quater slicers):

QtrA = values(Sheet1[Quarter])
QtrB = values(Sheet1[Quarter])

 

Step3. Adjust your measures as below:

Measure-1 = CALCULATE(SUM(Sheet1[Balance]),FILTER(Sheet1, Sheet1[Quarter]=SELECTEDVALUE(QtrA[Quarter])))
Measure-2 = CALCULATE(SUM(Sheet1[Balance]),FILTER(Sheet1, Sheet1[Quarter]=SELECTEDVALUE(QtrB[Quarter])))
 
Step4. 
make 2 slicers with QtrA & QtrB (remove your Quater1 and Quater2 from the canvas)
make bar chart:  Axis: Category, Values: Measure-1 and Measure-2

 

Hope this helps.

View solution in original post

2 REPLIES 2
Helper II
Helper II

@parag123 

Hi parag,

If you make 2 date-slicers which exists in the same table, it would slice each other ending up having null table.

the solution would be introducing two calculate tables which can be used as slicers.

 

From your data,

Step1. Create calculated quater column in the main table (as you made for [quater1] and [quater2]):

Quarter = CONCATENATE(FORMAT([Date],"YYYY"), CONCATENATE(" Q",ROUNDUP(MONTH([Date])/3,0)))

 

Step2. Create two tables (Sellecting quater slicers):

QtrA = values(Sheet1[Quarter])
QtrB = values(Sheet1[Quarter])

 

Step3. Adjust your measures as below:

Measure-1 = CALCULATE(SUM(Sheet1[Balance]),FILTER(Sheet1, Sheet1[Quarter]=SELECTEDVALUE(QtrA[Quarter])))
Measure-2 = CALCULATE(SUM(Sheet1[Balance]),FILTER(Sheet1, Sheet1[Quarter]=SELECTEDVALUE(QtrB[Quarter])))
 
Step4. 
make 2 slicers with QtrA & QtrB (remove your Quater1 and Quater2 from the canvas)
make bar chart:  Axis: Category, Values: Measure-1 and Measure-2

 

Hope this helps.

View solution in original post

@colacan this is perfect. Thank you so much.

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors