cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Calculate Median and Change over Time

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!

1 ACCEPTED SOLUTION
Community Champion

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
)
```

Thank you for the kudos 🙂

Proud to be a Super User!

8 REPLIES 8
Community Champion

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

as described here:

Thank you for the kudos 🙂

Proud to be a Super User!

Anonymous
Not applicable

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!

Community Champion

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

Thank you for the kudos 🙂

Proud to be a Super User!

Anonymous
Not applicable

Community Champion

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
)
```

Thank you for the kudos 🙂

Proud to be a Super User!

Anonymous
Not applicable

Thank you!! It works

Anonymous
Not applicable

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)?

Community Champion

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

Thank you for the kudos 🙂

Proud to be a Super User!

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors