jeroentjev
New Member
- Joined
- Aug 5, 2014
- Messages
- 2
Hi all,
Hope you can help me with the following question;
I have a couple of columns with data:
- Column A consists of numeric data (revenue growth in %)
- Column B consists of numeric & Text data (year: 2016, 2017, 2018 and current)
- Column C consists of text fields (different geographical areas)
- Column D consists of text fields (different companies in the geographical areas)
I want to calculate the difference of the median in revenue growth within a geographical area over 2 time periods (the time periods [column B] and geographical area [column D] can be selected on a dashboard) (Are you still with me?)
I'm using the following formula:
Median of rev growht (of latest year [in B], for selected geo-area [in C]) - Median of rev growht (of latest year - 1 [in B], for selected geo-area [in C])
Somehow I can't figure out how I should code this in DAX. Currently it looks like this:
Revenue growth (%) =
var previousdate = CALCULATE(MAX('Table'[Year]);FILTER(ALLEXCEPT('Table';'Table'[Company]);'Table'[Year]<MAX('Table'[Year]))
)
var previousvalue = LOOKUPVALUE('Table'[Revenue growth];'Table'[Year];previousdate;'Table'[Company];MAX('Table'[Company]))
return IF(ISBLANK(previousvalue);BLANK();(MEDIAN('Table'[Revenue growth])-previousvalue)*10000)
MANY THANKS!
Hope you can help me with the following question;
I have a couple of columns with data:
- Column A consists of numeric data (revenue growth in %)
- Column B consists of numeric & Text data (year: 2016, 2017, 2018 and current)
- Column C consists of text fields (different geographical areas)
- Column D consists of text fields (different companies in the geographical areas)
I want to calculate the difference of the median in revenue growth within a geographical area over 2 time periods (the time periods [column B] and geographical area [column D] can be selected on a dashboard) (Are you still with me?)
I'm using the following formula:
Median of rev growht (of latest year [in B], for selected geo-area [in C]) - Median of rev growht (of latest year - 1 [in B], for selected geo-area [in C])
Somehow I can't figure out how I should code this in DAX. Currently it looks like this:
Revenue growth (%) =
var previousdate = CALCULATE(MAX('Table'[Year]);FILTER(ALLEXCEPT('Table';'Table'[Company]);'Table'[Year]<MAX('Table'[Year]))
)
var previousvalue = LOOKUPVALUE('Table'[Revenue growth];'Table'[Year];previousdate;'Table'[Company];MAX('Table'[Company]))
return IF(ISBLANK(previousvalue);BLANK();(MEDIAN('Table'[Revenue growth])-previousvalue)*10000)
MANY THANKS!