Forum Discussion

Jyaulhaq's avatar
Jyaulhaq
Frequent Visitor
2 years ago
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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

hi  
try below dax measures

1.AdjustedHoursAndDay = VAR CurrentHours = 'YourTable'[Hours] VAR CurrentDay = 'YourTable'[Day] VAR PreviousHours = CALCULATE(MAX('YourTable'[Hours]), FILTER('YourTable', 'YourTable'[Day] = EARLIER('YourTable'[Day]) - 1)) VAR PreviousDay = CALCULATE(MAX('YourTable'[Day]), FILTER('YourTable', 'YourTable'[Day] = EARLIER('YourTable'[Day]) - 1)) RETURN IF(ISBLANK(CurrentDay), CurrentHours + PreviousHours, CurrentHours)


2.

Top1HoursAndDay = 
VAR TopHours = CALCULATE(MAX('YourTable'[AdjustedHoursAndDay]))
VAR TopDay = CALCULATE(MAX('YourTable'[Day]), FILTER('YourTable', 'YourTable'[AdjustedHoursAndDay] = TopHours))
RETURN
CONCATENATE(TopHours, " hours and ", TopDay, " day")

 

Jyaulhaq

Jyaulhaq's avatar
Jyaulhaq
Frequent Visitor
2 years ago

i have Hours and Day measures, not column. Please do needful