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
Daniel29195's avatar
Daniel29195
Community Champion
2 years ago

manup07 

 

maybe i dont yet quite fully understand the problem ,

 

you need to calculate the nb of nigts per each room , per year-month 

 

 

so per example ,  

if you have the following data : 

room           arrival date          depart date              

r1                   2023-01-01         2023-01-10            

r2                  2023-01-12         2023-01-20

 

so for the month-year 2023 - 01 , you have 10 +  8  =  18 nights . 

 

if this is the logic . 

try this measure :

measure =  

VAR result= 
           sumx(

                   addcolumns(

                          values ( RESERVATIONS) , 

                          "@added_column" ,  datediff ( start_date ,  end_date , days ) 
                   )
         )


RETURN

             result 

 

 

let me know if it works. if not, it would helpful if you can share sample data to take a closer look  and help you out with your problem . 

 

 

If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. Dont forget to hit that  thumbs up button 🫡👍

  • manup07's avatar
    manup07
    Frequent Visitor
    2 years ago

    Hi Daniel29195 Thank you for all your help.

     

    What I am trying to solve, is getting the total of each row in my matrix to be right, as yo can see in the first image I'm getting nothing or an incorrect answer. Example:

    My matrix  first row says in 2024-ene=54 and 2024-feb=7 the total should be 61 no 0 or blank, last row says 35 so the total should be 35 but I'm getting nothing,  and so on. I really appreciate the solution to get the correct answers.

     

    tks.