DAX Selectvalue measure Totals not showing in a table

[VTSDEV]

Hi All,

I have recently developed a measure which calculates a cummulative average (GP Final) of a particular week day during a set period of time (last one year) and averages of a public holiday if the day is a public holiday. The measure works perfectly but i was unable to obtain the totals in the totals row of the table.

DAX formulea - 

Disp Daily Average =
VAR MaxDate = MAX( 'Date'[Date] ) -- retrieve latest date
VAR MinDate = EDATE( MaxDate, -12 ) -- move it back 12 months
VAR Result =
if(CONTAINSSTRING(SELECTEDVALUE('Date'[State for Public Holiday]),SELECTEDVALUE('Main Table'[SSTATE])),CALCULATE(
SUM('Main Table'[GP Final])/COUNT('Date'[Date]),'Main Table'[SCODELEVEL1] = "DISPENSARY",
FILTER( ALL( 'Date' ), -- return period between
CONTAINSSTRING('Date'[State for Public Holiday],SELECTEDVALUE('Main Table'[SSTATE]))-- bigger than year before
)),CALCULATE(
SUM('Main Table'[GP Final])/COUNT('Date'[Date]),'Main Table'[SCODELEVEL1] = "DISPENSARY",
FILTER( ALL( 'Date' ), -- return period between
'Date'[Date] <= MaxDate && -- latest date
'Date'[Date] > MinDate && 'Date'[Weekday] = SELECTEDVALUE('Date'[Weekday]) -- bigger than year before
)))
RETURN
Result

Table - 

Any help will be greatly appreciated

[amitchandak]

@VTSDEV , In such case you have to change your return using summarize or values

Example

return

refer

https://www.youtube.com/watch?v=ufHOOLdi_jk

Sumx(addcolumns(summarize(Date, Date[Date]), "_1", result ), [_1]_

[VTSDEV]

Unfortunately, I think it didnt work. Or maybe i am missing something?

[v-chenwuz-msft · Accepted solution]

Hi @VTSDEV ,

You want to calculate total in total rows please to hasonevalue(). This function is often used to control if the content is the total rows. If TRUE, return the calculation for total, FALSE, return calculation for items.

HASONEVALUE function (DAX) - DAX | Microsoft Docs

If I misunderstood you please let me know.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.