Forum Discussion
Getting totals to work in a complex matrix visual
- 4 years ago
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
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