topic Re: Creating a sum measure of a complicated measure in DAX Commands and Tips

Creating a sum measure of a complicated measure

Vatz8 — Tue, 22 Oct 2024 01:21:34 GMT

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.

Re: Creating a sum measure of a complicated measure

lbendlin — Tue, 22 Oct 2024 16:15:35 GMT

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

Re: Creating a sum measure of a complicated measure

Vatz8 — Tue, 22 Oct 2024 20:09:18 GMT

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.

Re: Creating a sum measure of a complicated measure

lbendlin — Tue, 22 Oct 2024 20:14:18 GMT

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-Forum/ba-p/963216

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/1447523

Re: Creating a sum measure of a complicated measure

Vatz8 — Tue, 22 Oct 2024 20:29:23 GMT

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

Re: Creating a sum measure of a complicated measure

lbendlin — Tue, 22 Oct 2024 20:31:03 GMT

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-Forum/ba-p/963216

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/1447523

Re: Creating a sum measure of a complicated measure

Anonymous — Wed, 23 Oct 2024 07:05:03 GMT

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.

Re: Creating a sum measure of a complicated measure

Vatz8 — Mon, 28 Oct 2024 17:57:33 GMT

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.