topic Re: Not summing up correctly at the Facility Level in my matrix in DAX Commands and Tips

Not summing up correctly at the Facility Level in my matrix

ihmerr2c — Fri, 29 Dec 2023 22:15:19 GMT

Hi so i have this measure

TotalAmountMeasure =

CALCULATE(

SUM('BillingServiceRollup'[Amount]),

'EMRToBilling'

)
it feeds into an allocation meaure that im making

AllocatedAmountp =

CALCULATE (

IF(

SELECTEDVALUE('EMRToBilling'[bsr_Visits]) > 1,

[TotalAmountMeasure] * DIVIDE([ServedHoursMeasure], [TotalServedHoursWeekMeasure]) * MAX('mediware vw_EMRToBilling'[Contractper]),

IF(

COUNTROWS(FILTER('EMRToBilling', 'EMRToBilling'[feeschedc] = "Unknown")) > 0,

[TotalAmountMeasure] * 1,

[TotalAmountMeasure] * MAX('EMRToBilling'[Contractper])

)

)
For some reason its doing its working 100% correctly at the client level, but at the facility level its showing the results for the TotalAmountMeasure. The data model is correct so idk why its ignoring the measure and totalling the total amount measure for the facilities. Its also not showing the totals for the rows that the ('[bsr_Visits]) > 1 is referencing .

in the matrix in using dimfacility[facilityname]
dimclient [fullname]
dimdate [datevalue]

they are all related to the fact

Re: Not summing up correctly at the Facility Level in my matrix

jjrand — Fri, 29 Dec 2023 23:26:56 GMT

Hi, I think I might be able to help with this, but I would need some sample data. Are you able to provide that? Thanks

Re: Not summing up correctly at the Facility Level in my matrix

ihmerr2c — Sat, 30 Dec 2023 00:01:25 GMT

I hope so! What all would you need!

Re: Not summing up correctly at the Facility Level in my matrix

jjrand — Sat, 30 Dec 2023 00:46:52 GMT

I would need the tables that are used in calculating your measures. BillingServiceRollup and EMRToBilling, and any other ones that are relevant, in a format which is easy to copy and paste. Nobody is going to hand type data from an image. Often real tables include sensitive information and/or are too big to attach. In that case, a representative sample is ideal. Something in the same shape as your original data.

I would then need a screenshot of your model, to see how the tables are related and if you have any dimension tables, such as Date, Customer, Facility or similar.

Finally, an image of the output you are getting, and then an image of desired behaviour, which you can make as a table in excel. If you can't, simply describing it should be fine.

This seems like a lot of work, but remember that people here are volunteering their own personal time, and you should make it as easy as possible for them to help you.

Please refer to this article

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-Get-Your-Question-Answered-Quickly/m-p/1626726#M32906

Re: Not summing up correctly at the Facility Level in my matrix

ihmerr2c — Sat, 30 Dec 2023 01:57:11 GMT

So the total amount measure is bringing back the raw value from the billing table, but there are instances where the amount needs to be broken down further, because it summing the amount up across all days of the week. So I had to allocate it by dividing the hours for that day into the total hours for that week, multiply that by the amount total for that week and if it the bsr_visit key is >1 it allocates the amount. The issue is now that im doing that its not totalling correctly a the facility level. I need the allocated amount to bring back the true sum and its only bringing back the total amount measure sum before the allocation. Its also not brigning back the rows totals for the value>1

TotalAmountMeasure =

CALCULATE(

SUM('mediware dimBillingServiceRollup'[Amount]),

'mediware vw_EMRToBilling'

)

ServedHoursMeasure =

DIVIDE(

CALCULATE(

SUMX(

FILTER(

'mediware factEMRToBilling',

'mediware factEMRToBilling'[isDeleted] = 0

'mediware factEMRToBilling'[ServedMinutes]

USERELATIONSHIP('dimDate'[DateValue], 'mediware factEMRToBilling'[ServiceStartDate])

)

TotalServedHoursWeekMeasure =

VAR CurrentDate = SELECTEDVALUE(dimDate[DateValue])

VAR WeekStart =

SWITCH(

WEEKDAY(CurrentDate),

1, CurrentDate - 6, // Sunday

2, CurrentDate, // Monday

3, CurrentDate - 1, // Tuesday

4, CurrentDate - 2, // Wednesday

5, CurrentDate - 3, // Thursday

6, CurrentDate - 4, // Friday

7, CurrentDate - 5 // Saturday

)

VAR WeekEnd = WeekStart + 6

RETURN

CALCULATE(

[ServedHoursMeasure], // Use your actual servedhours measure here

FILTER(

ALL(dimDate),

dimDate[DateValue] >= WeekStart &&

dimDate[DateValue]<= WeekEnd

)

AllocatedAmountp =

CALCULATE (

IF(

SELECTEDVALUE('mediware vw_EMRToBilling'[bsr_Visits]) > 1,

[TotalAmountMeasure] * DIVIDE([ServedHoursMeasure], [TotalServedHoursWeekMeasure]) * MAX('mediware vw_EMRToBilling'[Contractper]),

IF(

COUNTROWS(FILTER('mediware vw_EMRToBilling', 'mediware vw_EMRToBilling'[feeschedc] = "Unknown")) > 0,

[TotalAmountMeasure] * 1,

[TotalAmountMeasure] * MAX('mediware vw_EMRToBilling'[Contractper])

)

Re: Not summing up correctly at the Facility Level in my matrix

ihmerr2c — Sat, 30 Dec 2023 18:10:44 GMT

Alright, so i got this measure to work, but it moves super slow in all visuals i attempt to use it in.

AllocatedAmountp2 =

CALCULATE (

SUMX (

SUMMARIZE (

'mediware vw_EMRToBilling',

'mediware dimFacility'[FacilityName], -- Use the correct column name

'mediware vw_EMRToBilling'[bsr_Visits],

'mediware vw_EMRToBilling'[feeschedc],

'mediware vw_EMRToBilling'[Contractper],

"TotalAmount", [TotalAmountMeasure],

"ServedHours", [ServedHoursMeasure],

"TotalServedHoursWeek", [TotalServedHoursWeekMeasure]

IF (

[bsr_Visits] > 1,

[TotalAmount] * DIVIDE([ServedHours], [TotalServedHoursWeek]) * [Contractper],

IF (

[feeschedc] = "Unknown",

[TotalAmount] * 1,

[TotalAmount] * [Contractper]

)

Any recs?