cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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! 

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II
Super User II

Re: Calculate Median and Change over Time

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
    )
Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Datanaut!

View solution in original post

8 REPLIES 8
Super User II
Super User II

Re: Calculate Median and Change over Time

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 

Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Datanaut!

Anonymous
Not applicable

Re: Calculate Median and Change over Time

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!

 

Super User II
Super User II

Re: Calculate Median and Change over Time

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

Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Datanaut!

Anonymous
Not applicable

Re: Calculate Median and Change over Time

I have added a link to a sample data file. 

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

Highlighted
Super User II
Super User II

Re: Calculate Median and Change over Time

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
    )
Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Datanaut!

View solution in original post

Anonymous
Not applicable

Re: Calculate Median and Change over Time

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

Super User II
Super User II

Re: Calculate Median and Change over Time

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

Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Datanaut!

Anonymous
Not applicable

Re: Calculate Median and Change over Time

Thank you!! It works 

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors