Chart Context and Normalization

[slsDrctr]

Hello, I have run up against an issue Youtube/Google University has not helped me solve yet.  Many have gotten me close, but none have given the final desired result. 

I have a chart with multiple series plotted against time.  I have been attempting to create a measure that will normalize the data (so each series starts at 0 on the Y axis) but am struggling since it seems my little brain isn't fully grasping how context/context modifiers work/s.  I have tried using Min/Max combined with ALLEXCEPT (and multiple other similar filter modifiers) and just can't seem to get to the desired result. 

Extra wishlist: I need all the page filters (which include things like month range filters, lines of business selection, etc) to still be applied and would like this measure to be reusable (so it will work with or without multiple series) as long as it's plotted against time.

I believe I want the measure to find the first value (so in the chart below it would be 1/1/2021, but this needs to be dynamic to work with the date filters on the page), then divide each value by that firstValue and subtract one.  Something like: normalizedValue = (currentValue / firstValue) -1.  The big issue is that it works on the page level, but on the chart the "firstValue" always ends up being whatever the current date value is, instead of the first date on the chart.

I'm most likely overcomplicating this and missing one key modifier so any help would be very much appreciated!

[v-angzheng-msft · Accepted solution]

Hi, @slsDrctr 

Try this:

Measure = 
var _firstValue=
CALCULATE(
    SUM('Table'[Sales]),ALL('Table'),
    'Table'[Date]=MINX(ALL('Table'),[Date])
    )
var _currentSale=MAX('Table'[Sales])
return DIVIDE(_currentSale,_firstValue)-1

Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ValtteriN]

Hi,

I am not fully I understood your issue. You want your measures to start from 0? This would mean that the data returned would also have to be 0. E.g. if business description is "Electrical and they have sales on 1.1.2021 basic sum measures will returns values that are >0. This means that it isn't feasible for them to start from 0. If you want to do cumulative calculations that "reset" every year. I would use DATESYTD or patterns described here: https://www.sqlbi.com/articles/computing-running-totals-in-dax/

Could you describe the issue a bit more? E.g. what is the expected end result of this measure

[slsDrctr]

Thanks for the response.  When I say "start at 0" I mean to normalize all the subequent values to a percentage of the first value.  That way I can see how series that have different ranges compare to each other over time and more easily spot divergences.

Example in table form:

Row#	Date	Sales	Normalized Value
0	1/1/2022	100 (firstValue)	0 =  (100/100)-1
1	1/2/2022	110	.10 = (110/100)-1
2	1/3/2022	105	.05
3	1/4/2022	120	.2
4	1/5/2022	115	.15
5	1/6/2022	120	.2
6	1/7/2022	125	.25

The big issue is that when I attempt to find the sales value for the firstdate in the context I always get the date of the current row.  So for example, when I'm trying to solve for row 4 I need the sales value from row 0, but I'm getting the sales value for row 4 instead.

Here is the current formula I'm using (this is one of a dozen or more iterations I've tried):

Thank you!