Getting totals to work in a complex matrix visual

[SteveNoga]

I am creating a Matrix Table that estimates future work by month.  Work exists in the form of a Ticket. 

There are two tables involved:  a (1) Ticket Table and a (2) Calendar Table.  The ticket table contains 1 entry per ticket.  The Calendar table provides a “workday” column that is set to 1 for work days and 0 for weekends and holidays.

Ticket entries are refreshed daily with the total amount of effort spent against the case.  This value is subtracted from an estimate of the total effort required to complete the Ticket to produce an estimate of remaining effort.  

The remaining effort is divided by the number of remaining business days till ticket completion to get a remaining effort per day value.   This value is then applied as part of a measure to each future month as appropriate.   I believe it is this activity which is over-complicating my model and making it difficult to get accurate totals for the Monthly columns.  Below is a working draft of the matric table

In this draft I am showing two columns per month.  Both use the same measure function (see screen prints below).  However, one is based on the use of the SUM function while the other uses SUMX.

The SUM Column displays exactly what I need except the column totals are incorrect for reasons well covered in forums such as this one.  Here is the measure producing this column.

The version of this measure producing the error is below.  I have tried numerous iterations and come away without success.  If there is an obvious approach for getting this to work I would love to see it.  However, I am also open to alternative ways of allocating effort by month across future workdays.   

I did try to apply SUMX at the BuldDays level, but in doing so SUMX iterates against the  Calendar table instead of the ticket table.

Any ideas or suggestions would be greatly appreciated.

I do have a PBIX file I can share, but the forum will not let me attach it and my OneDrive will not permit a share all.

[SteveNoga]

I have simplified my presentation of the problem and made an advancement.  Hopefully this increases the odds of someone being able to show me the error in my approach.

First,,,,, Here is a display of my problem..

All values except for the totals for SUMX and SUMX1b  are correct.

Column SUMX shows the results using my original scrript above.  Which is comprised of a Daily Rate x WorkDays

Column SUMX1a shows the results of a script dispalying just the Daily Rate.  This works as required.

Column SUMX1b shows the results of a script displaying the Daily Rate x WorkDays.  The error with this calculation implies the issue is with the WorkDays calculation.

Here is the code for Column SUMX1a that is totaling correctly.

SUMX1a = 
VAR MinContextDate = MIN(CalendarTableV1[Date]) 
VAR MaxContextDate = MAX(CalendarTableV1[Date]) 
‘////////////////////////////////////////////////////////////////////////////////////////////// 
VAR FcstStart = CALCULATE(MAX(TicketDataTable[Pool Fcst Start Date]),ALL(CalendarTableV1)) + 1 
VAR FcstFinish = CALCULATE(MAX(TicketDataTable[Targetfinish_date]),ALL(CalendarTableV1)) 

VAR Fcst_Start = IF (FcstStart <= MinContextDate 
                                   && FcstFinish > MinContextDate, MinContextDate, 
                              IF (FcstStart > MinContextDate 
                                  && FcstStart < MaxContextDate, FcstStart, BLANK())) 
VAR Fcst_Finish = IF (FcstFinish <= MinContextDate, BLANK(), 
                                IF (FcstFinish > MinContextDate 
                                    && FcstFinish <= MaxContextDate, FcstFinish , 
                                IF (FcstFinish > MaxContextDate 
                                   && FcstStart < MaxContextDate, MaxContextDate, BLANK()))) 
////////////////////////////////////////////////////////////////////////////////////////////// 
VAR _BusDayTable = CALCULATETABLE(SUMMARIZE(CROSSJOIN(TicketDataTable,CalendarTableV1) 
                                               ,[Ticketid] 
                                               ,[PoolFcsthrsPerCasePerBusDay]
                                               ,"WorkDays" ,SUM(CalendarTableV1[WorkDays])) 
                                         ,FILTER((CalendarTableV1),FcstStart <= [Date] && FcstFinish >= [Date])) 
////////////////////////////////////////////////////////////////////////////////////////////// 
VAR PoolHrsPerCasePerBusDay = 
        CALCULATE(SUMX(_BusDayTable,TicketDataTable[PoolFcsthrsPerCasePerBusDay] ), 
                   FILTER(CalendarTableV1 ,Fcst_Start <= [Date] && Fcst_Finish >= [Date])) 

return PoolHrsPerCasePerBusDay 

The only difference between the SUMX1a and SUMX1b is the definiton of PoolHrsPerCasePerBusDay .

In SUM1a  we have

       CALCULATE(SUMX(_BusDayTable,TicketDataTable[PoolFcsthrsPerCasePerBusDay] ),

In SUM1b we have

       CALCULATE(SUMX(_BusDayTable,TicketDataTable[PoolFcsthrsPerCasePerBusDay] * [WorkDays] ),

I can provice a .pbix file for anyone interested in taking a closer look. 

Thanks

[SteveNoga]

Administrator....  The last two responses to my request were from me. 

I would really appreciate some help. 

Can share a PBIX file if it would help but need and email address to send it to.

From: Power BI Community Administrator <mailer@us.khoros-mail.com>
Sent: Saturday, January 22, 2022 9:38 PM
To: Noga, Stephen F <stephen.noga@dxc.com>
Subject: Did you get the answer you needed?

Hello SteveNoga,

Your topic recently received a reply.

Topic: Getting totals to work in a complex matrix visual
Date: 01-17-2022 05:29 PM

Did it solve your problem?

Click here to view the reply and mark one as an Accepted Solution.

This helps others find helpful answers in the community too!

[mahoneypat · Accepted solution]

Please see this video. You will need to reference your existing measure in a new measure using the pattern below (and use it in your visual instead).

(2) Power BI - Tales from the front #01 - Getting the Right Total - YouTube

NewMeasure = SUMX(VALUES(Table[TicketID]), [Your Measure])

Pat

[SteveNoga]

Pat.   First... I am sorry I did not try this earlier.  I believce I missed it.

It seems to work  🙂 

 Not yet sure why it work yet.  Hopefully the video will fill in the gaps. 

Not sure I would have gotten this on my own,.

[SteveNoga]

I can email you a copy of the PBIX file if it woulf be helpful

[SteveNoga]

I changed the existing SUMX measure to:  return  SUMX(VALUES(TicketDataTable[Ticketid]), BusDays * PoolHrsPerCasePerBusDay)

There was no change to the Matrix visual.  Incorrect totals and missing values persist.