Creating a sum measure of a complicated measure

[Vatz8]

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.

[v-yajiewan-msft]

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.

[Vatz8 · Accepted solution]

Hi, Thank you for the reply. Based on you suggestion and with a few modifications, I finally got the solution. Because my data had multiple tables , Allselected could not be used directly so I had to modify it a bit.

Total Days =

    CALCULATE(

        SUMX(Table2,[Days]),

        ALLSELECTED(Table[Skilled Stay Range]),

        ALLSELECTED(Table2[Full name]),

        ALLSELECTED(Table3[Facility])

    )

This now works perfectly. Thank you for the direction. Appreciated.

[lbendlin]

SUMX(ADDCOLUMNS(SUMMARIZECOLUMNS(Table1[Skilled Stay Range],Table2[Full name],table3[Facility]),"Sum",CALCULATE([Days])), [Sum])

[Vatz8]

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.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Vatz8]

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

[lbendlin]

second request:Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...