cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SteveNoga
Frequent Visitor

Getting totals to work in a complex matrix visual

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

SteveNoga_0-1642468468386.png

 

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.

 

SteveNoga_1-1642468468393.png

 

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.

 

SteveNoga_2-1642468468399.png

 

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.

1 ACCEPTED SOLUTION
mahoneypat
Microsoft
Microsoft

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

6 REPLIES 6
SteveNoga
Frequent Visitor

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..

SteveNoga_0-1644736566174.png

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
Frequent Visitor

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
Microsoft
Microsoft

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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,.

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

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.

 

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors