Row and Grand Totals on Matrix Incorrect

RBDport · July 31, 2020, 5:36pm

Hi All,

Like many others I see on forum and elsewhere, I am having issues with row and grand totals within a Matrix. I definitely need to learn much more about DAX but have searched here and elsewhere and still can’t get my head around it. I believe the Summarize function is needed but logic may have been incorrect.

Any DAX formulas to assist would be greatly appreciated.

I have Total Payroll Hours Paid broken out by Employee within Hospital Job. I want to multiply these Paid Hours by a monthly Burden Rate and calculate Allocated Costs.
You can see from below screenshot that the Individual rows are accurate but the Row and Grand Totals are not. I would like for the Burden Rate and the Allocated Totals to be accurate. However, I may delete the Burden Rate column if inaccurate.

Total Labor Hours Paid = [Total Reg Hrs Paid] + [Total OT Hours] + [Total DT Hours]

Burden Rate for Month = sumx(filter(‘Burden Table’,‘Burden Table’[Date]), ‘Burden Table’[Burden Rate]) NOT SURE THIS IS BEST WAY or IF NEEDED[

Allocated Costs = [Burden Rate for Month] * [Total Labor Hours Paid]

Second issue is I would want any Hospital that had no Paid Hours not to show in visual. Screenshot below shows that the Burden Rate shows on every line. I would prefer to do this within the DAX formula rather than a filter.

I have uploaded a simplied version of the pbix file.

EDNA Example, Allocaion.pbix (455.2 KB)

Again, appreciate any help. Let me know if you need any additional info or have questions.

Richard

AntrikshSharma · July 31, 2020, 5:49pm

Like this?

RBDport · July 31, 2020, 5:57pm

Hi AntrikshSharma,

Thanks for the quick response. That looks okay but I would want the detail rows by hospital as well.

Thanks,

Richard

AntrikshSharma · July 31, 2020, 6:05pm

I see burden rate is only available for 2 hospitals

Burden Rate for Month =
CALCULATE (
    SUMX ( 'Burden Table', 'Burden Table'[Burden Rate] ),
    CROSSFILTER ( 'Payroll Export'[PR Week Ending Date], 'Date'[Date], BOTH )
)

RBDport · July 31, 2020, 7:15pm

Hi AntrikshSharma,

I tried your formula but it still has some issues with the totals and some other lines. The first screen shot you sent was more accurate. This is what I get with the same fields as first screenshot and formula provided. I have highlighted in yellow and red the issues still.

Appreciate your help.

Richard

mno · August 1, 2020, 1:46pm

Hi Richard,

Why don’t you try the following formulas?

Burden Rate for Month =
VAR Calc1 =
sumx(
filter(
‘Burden Table’,
‘Burden Table’[Date]),
‘Burden Table’[Burden Rate])
VAR Calc2 =
SUMX( VALUES( Jobs[Hospital Name]),
SUM( ‘Burden Table’[Burden Rate]))

Allocated Costs =
VAR Calc1 = [Burden Rate for Month] * [Total Labor Hours Paid]
VAR Calc2 =
SUMX(
VALUES( Employees[Last Name]),
[Burden Rate for Month] * [Total Labor Hours Paid])
RETURN
if (HASONEVALUE( Employees[Last Name]),
Calc1,
Calc2)

EDNA Example, Allocaion_v2.pbix (458.1 KB)

Have a good weekend

EnterpriseDNA · August 3, 2020, 3:08am

Hi @RBDport, did the response provided by the contributors help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

RBDport · August 3, 2020, 12:35pm

Hi mno,

Thanks for the help. I won’t be able to review your post and check it out till this afternoon hopefully (my month end work starts today). I will let you know then if it fixes the issue or if I have any questions. Thanks again.

Regards,

Richard

RBDport · August 3, 2020, 9:05pm

Hi mno,

I had a chance to review and changed one item on the Burden Rate for Month - from SUMX to AVERAGE and deleted the sum line. The Burden Rate should be an average of all the rates. My totals look like they are ok now. I also decided to keep the rate the same for everyone and for each month as well perhaps.

Questions for you in follow-up:

What is the significance of the

Also, can you help get rid of the extra lines that are blank as to Hospital - Hours. See below. I would prefer to do in DAX rather than a filter.

I appreciate your help. I understand the HASONEVALUE and context more with your help, and also how to correct the totals.

EDNA Example, Allocaion_v2 Adjusted.pbix (454.5 KB)

Thanks,

Richard

mno · August 3, 2020, 11:02pm

Hi Richard,

Not sure if I have understood your request, but I will try.

This is a very useful technique. But my apology, it shouldn’t have been written so poorly.

Better versions of the same calculation:

VALUES - creates virtual table from column "Jobs[Hospital Name]
ADDCOLUMNS - allows to add additional column,
“@Burden” - name of column which includes the following calculation: SUM (‘Burden Table’[Buden Rate])

Virtual table was created with 2 columns:
Hospital Name
SUM of Burden rate

Function SUMX sums the Burden Rate from the column “@Burden”.

One of the best techniques!. I will send you some links form EnterpriseDNA courses in a few days.

To get rid of any rows you can refer to other column and check if there is a value, if not you can make the number “3.87” blank().

Like this: IF( ISBLANK( [Total Labor Hours Paid]), blank(), …)

Burden Rate for Month =
VAR Calc1 =
sumx(
filter(
‘Burden Table’,
‘Burden Table’[Date]),
‘Burden Table’[Burden Rate])
VAR Calc2 =
AVERAGE(‘Burden Table’[Burden Rate])

RETURN
if ( HASONEVALUE( Jobs[Hospital Name]),
IF( ISBLANK( [Total Labor Hours Paid]), blank(),
Calc1),
Calc2)

If you have something else in mind just please let me know.

EDNA Example, Allocaion_v3 Adjusted.pbix (455.5 KB)

Take care!

Mariusz

RBDport · August 3, 2020, 11:34pm

Hi Mariusz,

Thank you for the detailed info. I will review tomorrow hopefully(tropical storm headed our way!)

I tried to use IF function but couldnt figure the syntax or where to place it. Thank you.

Regards

Richard

EnterpriseDNA · August 4, 2020, 1:43am

Please be sure to check out our DAX Clean Up tool it’s a great way to ensure to make your DAX code easy to read. Thanks!

https://analysthub.enterprisedna.co/dax-clean-up

mno · August 4, 2020, 7:44am

Thanks EnterpriseDNA. It seems to be much better

Burden Rate for MONTH =
VAR Calc1 = SUMX (
    FILTER ( 'Burden Table','Burden Table'[Date] ),
  'Burden Table'[Burden Rate]
) 
VAR Calc2 = AVERAGE ( 'Burden Table'[Burden Rate] ) 

RETURN
IF (
    HASONEVALUE ( Jobs[Hospital Name] ),
    IF ( ISBLANK ( [Total Labor Hours Paid] ), BLANK (), Calc1 ),
    Calc2
)

RBDport · August 4, 2020, 10:31pm

Hi Mariusz,

I appreciate all your help. This looks good and $ look okay, but I have one question. When I change the row layout of visual (currently works with this)

But, if I move say hospital to top of rows for visual (see below). The $ on Burden Rate and Allocation are now incorrect. I tried to change some DAX to fix but to no avail.

Will these measures only work with rows a certain way? Or does DAX have to be changed? Filters are not changed and remain the same.

Thanks again.

Richard

mno · August 5, 2020, 8:00am

HI Richard,

That’s right, when you change the layout of the table it has an impact on the function: HASONEVALUE.
The context on the table is crucial.

Why don’t you give this a go?
EDNA Example, Allocaion_v4 Adjusted.pbix (439.4 KB)

One change was required - in the following line: ```
IF ( HASONEVALUE ( ‘Date’[Month AND Year] )

I also moved line:
IF (
    ISBLANK ( [Total Labor Hours Paid] )


Before:

Burden Rate for MONTH =
VAR Calc1 = sumx (
    filter ( 'Burden Table','Burden Table'[Date] ),
  'Burden Table'[Burden Rate]
) 
VAR Calc2 = AVERAGE ( 'Burden Table'[Burden Rate] ) 

RETURN
IF (
    HASONEVALUE ( Jobs[Hospital Name] ),
    IF ( ISBLANK ( [Total Labor Hours Paid] ), blank (), Calc1 ),
    Calc2
)

After:

Burden Rate for MONTH =
VAR Calc1 = SUMX (
    FILTER ( 'Burden Table','Burden Table'[Date] ),
  'Burden Table'[Burden Rate]
) 
VAR Calc2 = AVERAGE ( 'Burden Table'[Burden Rate] ) 

RETURN
IF (
    ISBLANK ( [Total Labor Hours Paid] ),
    blank (),
    IF ( HASONEVALUE ( 'Date'[Month AND Year] ), Calc1, Calc2 )
)

Enjoy your day

Mariusz

RBDport · August 5, 2020, 1:25pm

Hi Mariusz,

Thanks for all your help with this. I’ve been trying for 3 weeks to get something to work. I definitely need to learn DAX more and row context, iterator process, etc. I learned COBOL, and RPG years ago (when using Punch Cards !!). A little easier today. Just need to learn the DAX language.

Again, thanks.

Be safe,

Richard