Calculate Median and Change over Time

[Anonymous]

Hi everyone,

I am hoping someone can help me with this. I am trying to calculate the median of margins for a particular subgroup, and compare it to the median of margins in the previous year, to create a "margin change" variable.

I have the following columns:

- Company

- Region

- Margin

- Year (2016, 2017, 2018 and 'current')

I want to compare the margin change per region. I am not able to use the PREVIOUSYEAR formulas because the Year column is in text format. 

I have tried the following calculation in DAX:

var lastyear = CALCULATE(MAX('Table'[Year]);FILTER(ALLEXCEPT('Table';'Table'[Company]);'Table'[Year]<MAX('Table'[Year])))

Var valuelastyear = CALCULATE(MEDIAN('Table'[Margin]);

FILTER( ALL('Table');'Table'[Year]=lastyear))
RETURN
CALCULATE(MEDIAN('Table'[Margin])-valuelastyear)

I really hope someone can help me! 

Here's a link to the sample data: https://drive.google.com/open?id=1IUdr_oFW8Y0ZaMVBSgIz7YcM3SZNUGfZ

[Stachu]

can you explain a bit more about your data model, and paste examples from the tables that you use?

as described here:

How to Get Your Question Answered Quickly 

[Anonymous]

My datamodel has many different queries with financial data. I have seperate tables for 'Year' and for 'Attributes' (which consists of Regions and other company details).

All tables in the datamodel are (many to one) related to Years and to Attributes table.

There are 33 companies in the data model. These companies can be categorized by regions (North America, Latin America, Europe, MEA, Asia Pacific). The Year, Region and Company variables are in text type. Unfortunately I cannot change the data type of 'Year' into dates, because I have one date labeled as 'current'. The margin variable is in percentage. This profit margin variable is located in the 'Pre-Tax ROIC Analysis' table. 

My main issue is that I cannot seem to calculate the median of last year correctly. 

I hope this is enough for you to go on!

[Stachu]

no need to share the whole file, just few rows from each relevant table, anonymised so there would be no issue with showing it publicly

[Anonymous]

I have added a link to a sample data file. 

https://drive.google.com/open?id=1IUdr_oFW8Y0ZaMVBSgIz7YcM3SZNUGfZ

[Stachu · Accepted solution]

I'd suggest adding a calculated column to the Year table, such as this:

YearNr = IF('Year'[Year] = "Current", 2019, VALUE('Year'[Year]))

and as the new field is numeric you can then use math to refer to previous year (e.g. by subtracting 1)
e.g. like this:

Median Lastyear =
VAR __LastYear = MAX ( 'Year'[YearNr] ) - 1
RETURN
    CALCULATE (
        MEDIAN ( 'Pre-Tax ROIC Analysis'[EBIT/Revenue] ),
        ALL ( 'Year' ), -- we use ALL over the whole table because Year[Year] is in the visuals, not Year[YearNr]
        'Year'[YearNr] = __LastYear
    )

[Anonymous]

Thank you!! It works 

[Anonymous]

Thank you for the suggestion! Unfortunately I still have an issue. I have now used this calculation: 

Median LY = CALCULATE(MEDIAN('Pre-Tax ROIC Analysis'[EBIT/Revenue]);FILTER('Year';'Year'[YearNr]=MAX('Year'[YearNr])-1))

But is does not seem to be able to compute the median values. Do you have a solution how to create the median for region (last year)? 

[Stachu]

I edited the previous reply to include the measure example as well