How to get Sum on Total Row for a measure in DirectQuery?

[hemanthk]

Hi,

I have a measure to get the days between two dates and I want the total of this measure to be Sum, but it is giving me a MIN value on the total row of the Table Visual.

I am using DirectQuery.

My measure is like below:

1_WDays =

VAR BEGDATE = MIN('ZTAQ_CP03_V01'[Position Open (Posted) Date])

VAR ENDDATE = MIN('ZTAQ_CP03_V01'[Position Hired Date])

VAR HOLIDAYS = CALCULATE(DISTINCTCOUNT('BU Holidays'[Date]),FILTER('BU Holidays', 'BU Holidays'[Date] >= BEGDATE && 'BU Holidays'[Date] <= ENDDATE))

VAR WEEKENDS = CALCULATE(DISTINCTCOUNT(CAL[Date]),FILTER(CAL, CAL[Date] >= BEGDATE && CAL[Date] <= ENDDATE && WEEKDAY(CAL[Date], 2) > 5))

RETURN (DATEDIFF(BEGDATE, ENDDATE, DAY) - HOLIDAYS - WEEKENDS)

Can you please suggest how to get the Sum for this measure in DirectQuery.

[johnt75 · Accepted solution]

Rename your current measure to be "1_Wdays individual" then create a new measure

1_Wdays = IF( ISINSCOPE('Table'[Position number]), [1_Wdays individual],
var summaryTable = ADDCOLUMNS( SUMMARIZE( 'Table', 'Table'[Position number]), 
"@value", [1_Wdays individual])
return SUM( summaryTable, [@value])
)

[johnt75 · Accepted solution]

Rename your current measure to be "1_Wdays individual" then create a new measure

1_Wdays = IF( ISINSCOPE('Table'[Position number]), [1_Wdays individual],
var summaryTable = ADDCOLUMNS( SUMMARIZE( 'Table', 'Table'[Position number]), 
"@value", [1_Wdays individual])
return SUM( summaryTable, [@value])
)

[hemanthk]

Hi @johnt75 ,

Thank you for your response.

I tried the code as below:

I am getting error: Too many arguments passed to SUM Function.

Can you please let me know what should be corrected?

[hemanthk · Accepted solution]

Hi,

I fixed it by creating a New Table instead of another measure and adding DAX: ADDCOLUMNS and SUMMARIZE functions to it.

Thanks, John for your input.