Hi,
I have created a measure called [Days].It gives me the below output when put in Matrix.
I want to create a measure called [Total Days] which will be the total of the [Days] Measure. However, Days is a measure and directly the Sum function cannot be applied.
I want the [Total Days] to work by totalling the Days in visualization. This can vary as it is a measure and the measure varies depending on the slicer selections.
For the above example I want to the new measure should have the value =124 . That is totalling everything present in [Days] Measure.
I tried to create a measure called SUMAX=
SUMX(
SUMMARIZE(Table1,Table1[Skilled Stay Range],Table2[Full name],table3[Facility],"Sum",[Days]),
[Sum])
, but it only displays the total above. I cannot use this. I want to use the Total Days measure for yet another measure, which will essentially be Average Weighted= Divide(Days*Nursing CMI Real, Total Days)
The [Days} Measure is as follows-
Days =
VAR _rangeStart = FIRSTDATE(Table1[Effective_date - Copy])
VAR _rangeEnd = LASTDATE(Table1[Ineffective_date - Copy])
VAR _calendarStart = FIRSTDATE('Calendar'[Date])
VAR _calendarEnd = LASTDATE('Calendar'[Date])
RETURN
SWITCH(
TRUE(),
-- Condition 1: Calendar range is within Effective and Ineffective dates
_calendarStart >= _rangeStart && _calendarEnd >= _rangeEnd || _rangeEnd=BLANK(),
DATEDIFF(_calendarStart, _rangeEnd, DAY)+1,
-- Condition 2: Effective date is greater than the Calendar start, Ineffective date is greater than Calendar end
_rangeStart >= _calendarStart && _rangeEnd >= _calendarEnd || _rangeEnd=BLANK(),
DATEDIFF(_rangeStart, _calendarEnd, DAY)+1,
-- Condition 3: Both Effective and Ineffective dates are within the Calendar range
_rangeStart >= _calendarStart && _rangeEnd <= _calendarEnd,
DATEDIFF(_rangeStart, _rangeEnd, DAY)+1,
-- Condition 4: Both Effective and Ineffective dates are within the Calendar range
_rangeStart <= _calendarStart && _rangeEnd >= _calendarEnd || _rangeEnd=BLANK(),
DATEDIFF(_calendarStart, _calendarEnd, DAY)+1,
-- Default case, returning BLANK if no condition is met
BLANK()
)
Please let me know how can I solve this problem.
1 ACCEPTED SOLUTION
Hi @Vatz8 , hello lbendlin, thank you for your prompt reply!
Based on your description, we suggest you use AllSELECTED function to remove context filters from columns and rows in the current query, while retaining all other context filters or explicit filters.
I have created a simple test for your reference:
TotalDays2 = SUMX(
ALLSELECTED('Table'),
[Days]
)Result:
Best regards,
Joyce
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
It is giving me incorrect answer. I tried your solution under measure Sumaaa .I need 124 across. I even tried to directlly use it in my final measure but does not work.
Hi,
To make it clearer, this is the output for measure Total Days I am looking for.
It should be 124 all across so that my calculation for Weighted average can be correct
