Sub total Not working

[Anonymous]

Hii Everyone,

Can someone please help me to fix this Total.

Refer attached PBI file.

https://easyupload.io/vk2uer

Thank you.

[v-robertq-msft · Accepted solution]

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.

[Anonymous]

@Fowmy @Greg_Deckler @VahidDM 

[Anonymous]

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]

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

[Greg_Deckler]

@Anonymous This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907