Compare parts used between different years

[MaGar]

Hi all,

I'm stuck on trying to find way to compare part sizes used between 2020 and 2021.

Example of data:

2021 

5/8

5/8

3/4

3/4

3/4

1

1

1 1/2

1 /12

2020

5/8

5/8

5/8

3/4

1

1

1

1

1 1/2

1 1/2

I'm trying to show it on the column chart ie. in year 2020 5/8 was used so many time and in 2021 5/8 was used so many times and so on for other parts. Whatever I've tried I'm getting total used parts for one year and it is not broken down.

Any suggestions?

[MaGar]

To make it more simple. If part size 1 1/2 was used 10 times last year and only 5 times this year I need a graph to show that.

[v-jayw-msft]

Hi @MaGar ,

Is there a relationship between these two tables? And what is the axis for the visual in the screenshot? Please provide more information.

Best Regards,

Jay

[MaGar]

Hi @v-jayw-msft !

Thanks for taking time to reply.

I did try to create relationship but, I was getting below error. Can you give an example of how I create one? 

As far as Axis goes I have it set as 2020 installed by and 2021 installed by. I've tried many combinations but, I'm getting desired resault.

[v-jayw-msft]

Hi @MaGar ,

You may no need to create relationship. Use the 2021 as x-axis and use count of 2021 and the measure below as value.

count of 2020 = CALCULATE(COUNT(Table2[2020]),FILTER(Table2,Table2[2020]=SELECTEDVALUE(Table1[2021 ])))

Best Regards,

Jay

[MaGar]

Your screenshot shows exactly what I'm trying to achieve, but I have a hard time with DAX code. As you can see, I have Meter Size in both 2020 and 2021. What am I doing wrong?

[MaGar]

Is more clarification needed?

[v-jayw-msft]

Hi @MaGar ,

The formula is a measure not a column. Create a measure and add it to visual.

Please check the .pbix file as attached.

Best Regards,

Jay

[MaGar]

Hi @v-jayw-msft,

Your .pbix file is really helpful in trying to bring this to the finish line!

I renamed all tables to match and created a new measure.

Under values, you have 2021 and the measure? Tried all combinations but for some reason, I'm not getting end result.

[v-jayw-msft]

Hi @MaGar ,

Yes, I use count 2021 and the measure as values. Can you share the pbix file if you don't have any Confidential Information?

Best Regards,

Jay

[MaGar]

Hi @v-jayw-msft ,

Great idea! After I removed Confidential information and left only part sizes this is what I get.

https://drive.google.com/file/d/17G1NZ8aknsWPcDbxof8m4-D1lWrPajC6/view?usp=sharing

[v-jayw-msft · Accepted solution]

Hi @MaGar ,

I did some changes according to your data.

1# Since you have more groups in 2020, so I use 2020 as axis.

2# Since the axis is 2020, so I modify the measure to count 2021 as below.

count of 2021 = CALCULATE(COUNT(Table2[2021]),FILTER(Table2,Table2[2021]=SELECTEDVALUE(Table1[2020])))

So the visual is made of three parts: 2020 as axis, count of 2020 and measure 'count of 2021' as values.

Pbix as attached.

Best Regards,

Jay

[MaGar]

Thank you! This finally solved my issue.

[Fowmy]

@MaGar 

Not clear enough, please check on this link to get your question answered faster.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[MaGar]

Sorry about. I've included some screenshots of the problem and sample date. As you can one of the years does not show changes. It only shows the total for the entire year.

Let me know if more clarification is needed.

Chart2021 parts2020 parts