Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Chart Context and Normalization

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 multipl...
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    Hi, Anonymous 

    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's avatar
ValtteriN
Community Champion
4 years ago

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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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#DateSalesNormalized Value
01/1/2022100 (firstValue)0 =  (100/100)-1
11/2/2022110.10 = (110/100)-1
21/3/2022105.05
31/4/2022120.2
41/5/2022115.15
51/6/2022120.2
61/7/2022125.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!