Normalising a Time Series with Date Hierarchy

[Namdu]

Hey all,

Please allow me to thank you for reading this post. I have tried working on this problem for a full day with no results. I tried looking up other similar posts but could not find any.

I am an intermediate user of PBI and currently trying to normalise values on a line graph so that I can compare it to other data.

The line graph is shown below and it is simply sales revenue across a time period.

What I am trying to achieve:

I'd like the line graph to be normalised between values of 0 and 1 for any level of the date hierarchy (monthly,quarterly, etc) while also respecting page filters.

My Data:

I am not permitted to share the data, but it is as straightforward as you can imagine. There is a sales table of every transaction and a calendar table that i've connected to it.

The difficulty:

The challenge for me is the interaction between trying to normalising the summed values while also recognising the different date hierarchies. For example, the MAX(Revenue) just returns the maximum individual transaction, rather than the sum of the month/quarter. Every measure I have written has just resulted in junk results. The closest I have come was finding some similar requests that requires the use of ALLSELECTED, but I can't get it to work still.

Any help or guidance on where to look will be sincerely appreciated.

Regards,

Namdu

[OwenAuger · Accepted solution]

Hi @Namdu 

I think a visual calculation would work quite well here, since it calculates correctly regardless of the field on the chart's axis.

The traditional measure-based approach would need to detect the granularity of the axis, which is possible but a little tedious.

For visual calculations, you could create a visual calculation like this (assuming Revenue_Sum is the underlying measure):

Revenue Normalised = 
VAR MinRevenue = MINX ( ROWS, [Revenue_Sum] )
VAR MaxRevenue = MAXX ( ROWS, [Revenue_Sum] )
VAR CurrentRevenue = [Revenue_Sum]
VAR Result = DIVIDE ( CurrentRevenue  - MinRevenue , MaxRevenue - MinRevenue )
RETURN
    Result

Small example PBIX attached.

Would you be happy with this approach or would you like an example of a traditional measure-based approach?

Regards

[Namdu]

Hi Owen,

This is exactly it! Thank you so much!

I had absolutely no idea that visual calculations were even a thing. I had to look up on how to activate it for my PBI desktop. I stepped down in the date filter and it still works.

I notice the use of the keyword 'ROWS', this is my first time seeing this. So the way the code works is that it is looking at the visual's table's rows, rather than the underlying data. Then if we update the table, i.e change filter levels, the visual's tables will update (increase or reduce # of table rows) and the calculation will reapply itself again. Then the rest of the normalisation is as per normal. That's incredibly handy.

For my own learning, what would the traditional measure based approach look like? The summarising of the rows is where i got tangled up. I'd imagine it would address this!

Thank you again!

Regards,

Nam

[OwenAuger]

Glad to have helped 🙂

I should mention that visual calcs are still in preview, so there could be some changes coming, but I wouldn't expect the overall functionality to change.

As an alternative, I have added an example using a measure that you could write to achieve the same thing.

The measure needs have to cater for each possible column that could be used on the axis, so it ends up a bit long-winded. This is partly because DAX doesn't allow conditional table expressions, only conditional scalar expressions.

A measure like this needs to ensure that lower levels are tested for before higher levels of the date hierarchy, since ISINSCOPE ( <column> ) will be true for any column that is visible on the axis:

Revenue Normalised Measure = 
// Year = 1, Quarter = 2, Month = 3, Date = 4
VAR AxisLevel =
    SWITCH (
        TRUE (),
        ISINSCOPE ( 'Date'[Date] ), 4,
        ISINSCOPE ( 'Date'[Month] ), 3,
        ISINSCOPE ( 'Date'[Fiscal Quarter] ), 2,
        ISINSCOPE ( 'Date'[Fiscal Year] ), 1
    )
VAR MaxRevenue =
    CALCULATE (
        SWITCH (
            AxisLevel,
            4, MAXX ( VALUES ( 'Date'[Date] ), [Revenue_Sum] ),
            3, MAXX ( VALUES ( 'Date'[Month] ), [Revenue_Sum] ),
            2, MAXX ( VALUES ( 'Date'[Fiscal Quarter] ), [Revenue_Sum] ),
            1, MAXX ( VALUES ( 'Date'[Fiscal Year] ), [Revenue_Sum] )
        ),
        ALLSELECTED ( 'Date' )
    )
VAR MinRevenue =
    CALCULATE (
        SWITCH (
            AxisLevel,
            4, MINX ( VALUES ( 'Date'[Date] ), [Revenue_Sum] ),
            3, MINX ( VALUES ( 'Date'[Month] ), [Revenue_Sum] ),
            2, MINX ( VALUES ( 'Date'[Fiscal Quarter] ), [Revenue_Sum] ),
            1, MINX ( VALUES ( 'Date'[Fiscal Year] ), [Revenue_Sum] )
        ),
        ALLSELECTED ( 'Date' )
    )
VAR CurrentRevenue = [Revenue_Sum]
VAR Result =
    DIVIDE( CurrentRevenue - MinRevenue, MaxRevenue - MinRevenue )
RETURN
    Result

Regards

[Namdu]

Hi Owen,

This is exactly it! Thank you so much!

I had absolutely no idea that visual calculations were even a thing. I had to look up on how to activate it for my PBI desktop. I stepped down in the date filter and it still works.

I notice the use of the keyword 'ROWS', this is my first time seeing this. So the way the code works is that it is looking at the visual's table's rows, rather than the underlying data. Then if we update the table, i.e change filter levels, the visual's tables will update (increase or reduce # of table rows) and the calculation will reapply itself again. Then the rest of the normalisation is as per normal. That's incredibly handy.

For my own learning, what would the traditional measure based approach look like? The summarising of the rows is where i got tangled up. I'd imagine it would address this!

Thank you again!

Regards,

Nam