Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
@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]_
Unfortunately, I think it didnt work. Or maybe i am missing something?
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |