Forum Discussion
Solved
Top value
Dear Friend, i have two measures: Hours and Day as output data shown below, i want to get Top 1 Hours and Day based on Hours. result for below data: Top1: 56,758 hours and 23 Day Another ...
Hi Jyaulhaq
Below is an example I created to achieve the effect you want.
My sample:1. I created two measures to calculate the hours and days per month
Hours = CALCULATE(SUM('Table'[Hour]), ALLEXCEPT('Table', 'Table'[Month]))Days = CALCULATE(SUM('Table'[Day]), ALLEXCEPT('Table', 'Table'[Month]))2. Create several calculated columns as follow
rank = RANKX('Table', [Month], , ASC, Dense)Hours1 = VAR _lRank = [rank] - 1 VAR _lhours = MAXX(FILTER('Table', [rank] = _lRank), [Hours]) RETURN IF([Days] = BLANK(), [Hours] + _lhours, [Hours])Days1 = VAR _monthDays = DAY(EOMONTH([Month], 0)) VAR _lRank = [rank] - 1 VAR _lDays = MAXX(FILTER('Table', [rank] = _lRank), [Days]) RETURN IF([Days] = BLANK(), _lDays + _monthDays, [Days])Result:
Best Regards,
Yulia XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Jyaulhaq
Below is an example I created to achieve the effect you want.
My sample:
1. I created two measures to calculate the hours and days per month
Hours = CALCULATE(SUM('Table'[Hour]), ALLEXCEPT('Table', 'Table'[Month]))
Days = CALCULATE(SUM('Table'[Day]), ALLEXCEPT('Table', 'Table'[Month]))
2. Create several calculated columns as follow
rank = RANKX('Table', [Month], , ASC, Dense)
Hours1 =
VAR _lRank = [rank] - 1
VAR _lhours = MAXX(FILTER('Table', [rank] = _lRank), [Hours])
RETURN
IF([Days] = BLANK(), [Hours] + _lhours, [Hours])
Days1 =
VAR _monthDays = DAY(EOMONTH([Month], 0))
VAR _lRank = [rank] - 1
VAR _lDays = MAXX(FILTER('Table', [rank] = _lRank), [Days])
RETURN
IF([Days] = BLANK(), _lDays + _monthDays, [Days])
Result:
Best Regards,
Yulia Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.