topic Re: Compare Selected quarter on parallel bar chart in DAX Commands and Tips

Compare Selected quarter on parallel bar chart

Anonymous — Tue, 27 Oct 2020 16:32:10 GMT

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

Re: Compare Selected quarter on parallel bar chart

colacan — Tue, 27 Oct 2020 17:43:59 GMT

Anonymous

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.

Re: Compare Selected quarter on parallel bar chart

Anonymous — Tue, 27 Oct 2020 20:03:44 GMT

colacan this is perfect. Thank you so much.