Forum Discussion
DAX SORT ISSUE FOR RUNNING SUM
Hello All!
We are having the toughest time to get a correct sort in our DAX.
The table below has a column created in DAX called Cumulative Total. What we are doing is creating a cumulative total of the percentage. The problem is that the totaling is being done by the LineUtilization column and we really want it to be performed by the LineHours column (which we have created a rank for) . Our problem is no matter how we try and change the part ISONORAFTER('V_PBI_WO55_DETAILS'[LineUtilization],MAX('V_PBI_WO55_DETAILS'[LineUtilization]),DESC) we keep getting error messages. We wanted to have that ISONORAFTER to use the Ranking or the LineHours but we keep getting error messages as MAX cant be used with the variables or measures we created.
Hoping someone can help us!!
Anonymous,
Try these measures:
Total LineHours = SUM ( V_PBI_WO55_DETAILS[LineHours] ) PCT Unplanned DT = VAR vNumerator = SUM ( V_PBI_WO55_DETAILS[LineHours] ) VAR vDenominator = CALCULATE ( SUM ( V_PBI_WO55_DETAILS[LineHours] ), ALLSELECTED ( V_PBI_WO55_DETAILS ) ) VAR vResult = DIVIDE ( vNumerator, vDenominator ) RETURN vResult Rank LU = VAR vResult = RANKX ( ALLSELECTED ( V_PBI_WO55_DETAILS[LineUtilization] ), [Total LineHours] ) RETURN IF ( HASONEVALUE ( V_PBI_WO55_DETAILS[LineUtilization] ), vResult, BLANK () ) Cumulative Total = VAR vBaseTable = ADDCOLUMNS ( ALLSELECTED ( V_PBI_WO55_DETAILS[LineUtilization] ), "tmpPCT", [PCT Unplanned DT], "tmpRank", [Rank LU] ) VAR vRank = [Rank LU] VAR vResult = SUMX ( vBaseTable, IF ( [tmpRank] <= vRank, [tmpPCT], 0 ) ) RETURN IF ( HASONEVALUE ( V_PBI_WO55_DETAILS[LineUtilization] ), vResult, 1 )
2 Replies
Anonymous,
Try these measures:
Total LineHours = SUM ( V_PBI_WO55_DETAILS[LineHours] ) PCT Unplanned DT = VAR vNumerator = SUM ( V_PBI_WO55_DETAILS[LineHours] ) VAR vDenominator = CALCULATE ( SUM ( V_PBI_WO55_DETAILS[LineHours] ), ALLSELECTED ( V_PBI_WO55_DETAILS ) ) VAR vResult = DIVIDE ( vNumerator, vDenominator ) RETURN vResult Rank LU = VAR vResult = RANKX ( ALLSELECTED ( V_PBI_WO55_DETAILS[LineUtilization] ), [Total LineHours] ) RETURN IF ( HASONEVALUE ( V_PBI_WO55_DETAILS[LineUtilization] ), vResult, BLANK () ) Cumulative Total = VAR vBaseTable = ADDCOLUMNS ( ALLSELECTED ( V_PBI_WO55_DETAILS[LineUtilization] ), "tmpPCT", [PCT Unplanned DT], "tmpRank", [Rank LU] ) VAR vRank = [Rank LU] VAR vResult = SUMX ( vBaseTable, IF ( [tmpRank] <= vRank, [tmpPCT], 0 ) ) RETURN IF ( HASONEVALUE ( V_PBI_WO55_DETAILS[LineUtilization] ), vResult, 1 )