topic Re: Bar Graph Showing Percent Change per Category vs. Last Time Period in DAX Commands and Tips

Bar Graph Showing Percent Change per Category vs. Last Time Period

Aedranoth — Thu, 16 Jun 2022 18:46:37 GMT

Hello all,

Making a report but having trouble getting my desired visualization demonstrating percent change per category. Report pulls items (with a category column) produced in a week period along with value added (yield). Ideally, I'd like to create a bar chart that shows percent change of value added this week vs. last week for each category -- formatted like this:

The weighted average for total items yield current week and last week are correct.

To calculate percent change in total yield from this week over last week, I use this measure:

Percent Change Yield Alt = DIVIDE( 'Production 22'[Current Week Weighted Average Yield], 'Production 22'[Prior Week Weighted Avg Yield]) - 1

Using that measure I've been trying to create another that store the weight average for each category rather than as a whole (I'm sure this has something to do with row context but I'm relatively new to this so I'm not positive). I've attempted a few different variations of this trying with DISTINCT, CALCULATE, or anything else I can find--I get a similar result though.

At first I tried plugging in the measure "Percent Change Yield Alt" into the chart x-axis, which gives me this:

From what I can tell, when there are multiple instances of items belonging to the same product category, that category's percent change will default to 5.24 (or the percent change in yield for total week production).

Been trying some absolutely random stuff in order to try to get the correct numbers pertaining to each category. Here's one of my most recent efforts:

Average2Change = AVERAGEX( DISTINCT(SELECTCOLUMNS('Production 22', " <y-axis (categories)> ", 'Production 22'[Percent Change Yield Alt], "Percent Change Yield Alt", 'Production 22'[Percent Change Yield Alt])), [Percent Change Yield Alt] )

This DAX results in the first bar graph from earlier in this post.

Any help is appreciated.

Re: Bar Graph Showing Percent Change per Category vs. Last Time Period

lbendlin — Mon, 20 Jun 2022 01:13:39 GMT

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

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

Re: Bar Graph Showing Percent Change per Category vs. Last Time Period

Aedranoth — Mon, 20 Jun 2022 14:51:45 GMT

Hey, thank you for responding--

Here is a link to the pbix file.

Re: Bar Graph Showing Percent Change per Category vs. Last Time Period

Aedranoth — Mon, 20 Jun 2022 14:53:40 GMT

https://www.dropbox.com/scl/fi/etuea5caq97b8a3yqaea9/Sample-Weekly-Yields.xlsx?dl=0&rlkey=zag48a8etlsafuxrzpyixdm8j

Sample data as well, apologies

Re: Bar Graph Showing Percent Change per Category vs. Last Time Period

lbendlin — Mon, 20 Jun 2022 20:43:09 GMT

Please explain the importance of the Yield column. You have multiple different values for item1 /Cat 1/Week 1 - how to handle these? Quantity * Yield for each individual row? and then you want the weighted average of the yield over these rows?

Re: Bar Graph Showing Percent Change per Category vs. Last Time Period

Aedranoth — Mon, 20 Jun 2022 20:40:09 GMT

Different items can have different yield -- it is an indicator of how successful the production process was for each individual item. I'm using a weighted average based on the quantity produced to demonstrate yields per summarized item.

Re: Bar Graph Showing Percent Change per Category vs. Last Time Period

lbendlin — Mon, 20 Jun 2022 20:53:59 GMT

I see different yields for the same item - that's what got me confused.

Re: Bar Graph Showing Percent Change per Category vs. Last Time Period

Aedranoth — Mon, 20 Jun 2022 20:57:03 GMT

I see the confusion -- in that case it's different instances of production for the same item code -- so, it is the production of a different unit, but the same item/item name.

Re: Bar Graph Showing Percent Change per Category vs. Last Time Period

lbendlin — Mon, 20 Jun 2022 21:07:03 GMT

Anyway, here is the weighted average for the current week (which must be week 2 in your example)

weighted Avg = divide(sumx('Production 22',[Quantity]*[Yield]),sumx('Production 22',[Quantity]),0)

You can compute the same for the prior week and then do the WoW calculation. Looks like you already did that. Do you still need my help with that?

Re: Bar Graph Showing Percent Change per Category vs. Last Time Period

lbendlin — Mon, 20 Jun 2022 21:22:50 GMT

I simplified your file a little.

Prior Week Weighted Avg Yield = var PriorWeekVar = [Prior Week] var PW = CALCULATETABLE('Production 22',REMOVEFILTERS('Production 22'[Week No]),'Production 22'[Week Start]=PriorWeekVar) return DIVIDE ( SUMX ( PW, [Quantity] * [Yield]), SUMX ( PW, [Quantity] ),0 )

see attached

Re: Bar Graph Showing Percent Change per Category vs. Last Time Period

Aedranoth — Tue, 21 Jun 2022 13:17:50 GMT

This works, thank you so much!