Forum Discussion

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
10 years ago

Dealing with Measure Totals

This one has come up quite a bit recently. The issue surrounds using Measures in Table visualizations with a Total row. The complaint is that the "Total" row is "wrong" for the measure. Technically, ...
zahirr's avatar
zahirr
Regular Visitor
4 years ago

 

I want to calculate this 

 

And created the following measure - 

But says somewhere is wrong, but can't find it 😞 

 

 

IF(SUM ( 'MasterTbl'[Sett] ) = 1,

                                SUMX (
                (IF (
            SUM ( 'MasterTbl'[Sett] ) = 0,
            CALCULATE (
                SUM ( 'MasterTbl'[Total number of beneficiaries] ),
                ALLEXCEPT ( MasterTbl, 'MasterTbl'[Activities and Indicators.Activities] )
            ) )),

                 IF

            (
                VAR _categoryfirst =
                    CALCULATETABLE (
                        ADDCOLUMNS (
                            VALUES ( 'MasterTbl'[List of refugee settlements-Iran.settlements name] ),
                            "MaxValue",
                                CALCULATE (
                                    MAX ( 'MasterTbl'[Total number of beneficiaries] ),
                                    ALL ( 'MasterTbl'[Total number of beneficiaries] )
                                )
                        ),
                        ALL ( 'MasterTbl'[Total number of beneficiaries] )
                    )
                RETURN
                    CALCULATE (
                        MAX ( 'MasterTbl'[Total number of beneficiaries] ),
                        KEEPFILTERS (
                            TREATAS (
                                _categoryfirst,
                                'MasterTbl'[List of refugee settlements-Iran.settlements name],
                                'MasterTbl'[Total number of beneficiaries]
                            )) )) >= SUM('Settlement name'[population]), SUM('Settlement name'[population])),
            (
                VAR _categoryfirst =
                    CALCULATETABLE (
                        ADDCOLUMNS (
                            VALUES ( 'MasterTbl'[List of refugee settlements-Iran.settlements name] ),
                            "MaxValue",
                                CALCULATE (
                                    MAX ( 'MasterTbl'[Total number of beneficiaries] ),
                                    ALL ( 'MasterTbl'[Total number of beneficiaries] )
                                )
                        ),
                        ALL ( 'MasterTbl'[Total number of beneficiaries] )
                    )
                RETURN
                    CALCULATE (
                        MAX ( 'MasterTbl'[Total number of beneficiaries] ),
                        KEEPFILTERS (
                            TREATAS (
                                _categoryfirst,
                                'MasterTbl'[List of refugee settlements-Iran.settlements name],
                                'MasterTbl'[Total number of beneficiaries]
                            ) ) ) ) ),

                            (   IF (
            SUM ( 'MasterTbl'[Sett] ) = 0,
            CALCULATE (
                SUM ( 'MasterTbl'[Total number of beneficiaries] ),
                ALLEXCEPT ( MasterTbl, 'MasterTbl'[Activities and Indicators.Activities] )
            ),
    IF
        (
            (
                VAR _categoryfirst =
                    CALCULATETABLE (
                        ADDCOLUMNS (
                            VALUES ( 'MasterTbl'[List of refugee settlements-Iran.settlements name] ),
                            "MaxValue",
                                CALCULATE (
                                    MAX ( 'MasterTbl'[Total number of beneficiaries] ),
                                    ALL ( 'MasterTbl'[Total number of beneficiaries] )
                                )
                        ),
                        ALL ( 'MasterTbl'[Total number of beneficiaries] )
                    )
                RETURN
                    CALCULATE (
                        MAX ( 'MasterTbl'[Total number of beneficiaries] ),
                        KEEPFILTERS (
                            TREATAS (
                                _categoryfirst,
                                'MasterTbl'[List of refugee settlements-Iran.settlements name],
                                'MasterTbl'[Total number of beneficiaries]
                            )
                        )
                    )
            ) >= SUM('Settlement name'[population]), SUM('Settlement name'[population]),
            (
                VAR _categoryfirst =
                    CALCULATETABLE (
                        ADDCOLUMNS (
                            VALUES ( 'MasterTbl'[List of refugee settlements-Iran.settlements name] ),
                            "MaxValue",
                                CALCULATE (
                                    MAX ( 'MasterTbl'[Total number of beneficiaries] ),
                                    ALL ( 'MasterTbl'[Total number of beneficiaries] )
                                )
                        ),
                        ALL ( 'MasterTbl'[Total number of beneficiaries] )
                    )
                RETURN
                    CALCULATE (
                        MAX ( 'MasterTbl'[Total number of beneficiaries] ),
                        KEEPFILTERS (
                            TREATAS (
                                _categoryfirst,
                                'MasterTbl'[List of refugee settlements-Iran.settlements name],
                                'MasterTbl'[Total number of beneficiaries]
                            ) )  ) ) )  )))