Forum Discussion

manup07's avatar
manup07
Frequent Visitor
2 years ago
Solved

Wrong Column Subtotals

I need some help to solve this problem and understanding what's going on. I have the following matrix:   I am trying to get the Total column to be the sum of each row, but I get what...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi  manup07 ,

     

    Here are the steps you can follow：

    1. Create measure.

    Measure =
var _value1=
IF(
    HASONEVALUE('Table'[Year-Month]),[nights],SUMX(VALUES('Table'[Year-Month]),[nights]))
return
IF(
    HASONEVALUE('Table'[TIPOLOGIA]),_value1,SUMX(VALUES('Table'[TIPOLOGIA]),[nights]))

    2. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

  • manup07's avatar
    manup07
    2 years ago

    Thanks Anonymous , I could solved it aswell forcing the logic of it: 

    The following works for any matrix, just replace the values accordingly.
    VAR vTable2 =
        ADDCOLUMNS(
            CROSSJOIN(
                VALUES( columnName1),
                VALUES( columnName2)
            ),
            "randomName", [YourMeasure]
        )
    VAR varName =
        SWITCH(
            TRUE(),
            HASONEVALUE( columnName1 )
                && HASONEVALUE( columnName2 ), [YourMeasure],  //Condition A - base data rows
            HASONEVALUE( columnName2),                           //Condition B - force column totals
                CALCULATE(
                    SUMX(
                        vTable2,
                        [randomName]
                    ),
                    VALUES( columnName1 )
                ),
            HASONEVALUE( columnName1),                        //Condition C - force row totals
                CALCULATE(
                    SUMX(
                        vTable2,
                        [randomName]
                    ),
                    VALUES( columnName2)
                ),
                                                                         //Condition D - force grand total
            SUMX(
                vTable2,
                [randomeName]
            )
        )
    RETURN
        varName
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi  manup07 ,

 

Here are the steps you can follow：

1. Create measure.

Measure =
var _value1=
IF(
    HASONEVALUE('Table'[Year-Month]),[nights],SUMX(VALUES('Table'[Year-Month]),[nights]))
return
IF(
    HASONEVALUE('Table'[TIPOLOGIA]),_value1,SUMX(VALUES('Table'[TIPOLOGIA]),[nights]))

2. Result:

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

  • manup07's avatar
    manup07
    Frequent Visitor
    2 years ago

    Thanks Anonymous , I could solved it aswell forcing the logic of it: 

    The following works for any matrix, just replace the values accordingly.
    VAR vTable2 =
        ADDCOLUMNS(
            CROSSJOIN(
                VALUES( columnName1),
                VALUES( columnName2)
            ),
            "randomName", [YourMeasure]
        )
    VAR varName =
        SWITCH(
            TRUE(),
            HASONEVALUE( columnName1 )
                && HASONEVALUE( columnName2 ), [YourMeasure],  //Condition A - base data rows
            HASONEVALUE( columnName2),                           //Condition B - force column totals
                CALCULATE(
                    SUMX(
                        vTable2,
                        [randomName]
                    ),
                    VALUES( columnName1 )
                ),
            HASONEVALUE( columnName1),                        //Condition C - force row totals
                CALCULATE(
                    SUMX(
                        vTable2,
                        [randomName]
                    ),
                    VALUES( columnName2)
                ),
                                                                         //Condition D - force grand total
            SUMX(
                vTable2,
                [randomeName]
            )
        )
    RETURN
        varName