Forum Discussion
Sub total Not working
Hi,
According to your description, I can roughly understand your requirement, you are facing the error of subtotal not correct in the matrix, right? I think you can try to create a new measure like this:
Total Man Hours111 = var _summarize=SUMMARIZE('ProductionLinesTasks',[Order_Number],[IndexKey],"1",[Total Man Hours]) var _sum=SUMX(FILTER(_summarize,[Order_Number]=MAX('ProductionLinesTasks'[Order_Number])),[1]) return IF(ISINSCOPE('ProductionLinesTasks'[Order_Number])&&NOT(ISINSCOPE(ProductionLinesTasks[IndexKey])),_sum,[Total Man Hours])And you can go to the original matrix to place this measure to check the subtotal, you can find it displays correctly:
You can download my test pbix file below
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you. Greg_Deckler But My measure is very complicated.
Total Man Hours =
VAR __selordernumber =
MAX ( ProductionLinesTasks[Order_Number] )
VAR __selindexkey =
MAX ( ProductionLinesTasks[Indexkey] )
VAR __previousindex =
MAX ( ProductionLinesTasks[Index] ) - 1
VAR __nextindex =
MAX ( ProductionLinesTasks[Index] ) + 1
VAR __previousindexkey =
CALCULATE (
MAX ( ProductionLinesTasks[Indexkey] ),
FILTER (
ALL ( ProductionLinesTasks ),
ProductionLinesTasks[Index] = __previousindex
)
)
VAR __nextindexkey =
CALCULATE (
MAX ( ProductionLinesTasks[Indexkey] ),
FILTER (
ALL ( ProductionLinesTasks ),
ProductionLinesTasks[Index] = __nextindex
)
)
VAR __selteam =
MAX ( ProductionLinesTasks[Team_Name] )
VAR __nextstart =
CALCULATE (
MIN ( ProductionLinesTasks[Trans_DateTime] ),
FILTER (
ALL ( ProductionLinesTasks ),
ProductionLinesTasks[Action] = "Start"
&& ProductionLinesTasks[IndexKey] = __nextindexkey
)
)
VAR __previouscomplete =
CALCULATE (
MAX ( ProductionLinesTasks[Trans_DateTime] ),
FILTER (
ALL ( ProductionLinesTasks ),
ProductionLinesTasks[Action] = "Complete"
&& ProductionLinesTasks[IndexKey] = __previousindexkey
)
)
VAR __minstarttime =
IF (
ISBLANK (
CALCULATE (
MIN ( ProductionLinesTasks[Trans_DateTime] ),
FILTER (
ALL ( ProductionLinesTasks ),
ProductionLinesTasks[Action] = "Start"
&& ProductionLinesTasks[IndexKey] = __selindexkey
)
)
)
= TRUE (),
__previouscomplete,
CALCULATE (
MIN ( ProductionLinesTasks[Trans_DateTime] ),
FILTER (
ALL ( ProductionLinesTasks ),
ProductionLinesTasks[Action] = "Start"
&& ProductionLinesTasks[IndexKey] = __selindexkey
)
)
)
VAR __maxendtime =
IF (
ISBLANK (
CALCULATE (
MAX ( ProductionLinesTasks[Trans_DateTime] ),
FILTER (
ALL ( ProductionLinesTasks ),
ProductionLinesTasks[Action] = "Complete"
&& ProductionLinesTasks[IndexKey] = __selindexkey
)
)
)
= TRUE (),
__nextstart,
CALCULATE (
MAX ( ProductionLinesTasks[Trans_DateTime] ),
FILTER (
ALL ( ProductionLinesTasks ),
ProductionLinesTasks[Action] = "Complete"
&& ProductionLinesTasks[IndexKey] = __selindexkey
)
)
)
VAR __summarydata =
COUNTROWS (
FILTER (
Teams_Operators,
Teams_Operators[Team_Name] = __selteam
&& Teams_Operators[Join_DateTime] <= __minstarttime
&& Teams_Operators[Leave_DateTime] >= __maxendtime
)
)
RETURN
IF (
__summarydata > 0,
SUMX (
SUMMARIZE (
ProductionLinesTasks,
ProductionLinesTasks[Indexkey],
"Total", DATEDIFF ( __minstarttime, __maxendtime, MINUTE )
),
[Total]
) * __summarydata,
BLANK ()
)Hard to figure out from where exactly the error comes.
- Greg_Deckler
Community Champion
Anonymous Doesn't matter how complicated the measure is as long as it returns the correct value at the row level. Then it is just a matter of summarizing it correctly in a table variable and doing the correct aggregation across it.
