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 you see instead. I am using the following measure: 

nights =
VAR _currentmonth =
    MAX ( 'Calendar'[Year-Month sort] )
VAR _t =
    GENERATE (
        FILTER (
            RESERVATIONS,
            NOT RESERVATIONS[ChannelLimpio]
                IN { "Owner", "Mantenimiento", "Directo - Cortesia" }
        ),
        FILTER (
            'Calendar',
            'Calendar'[Date] >= RESERVATIONS[Arrive]
                && 'Calendar'[Date] < RESERVATIONS[Depart]
        )
    )
RETURN
    COUNTROWS ( FILTER ( _t, 'Calendar'[Year-Month sort] = _currentmonth ) )
 
I'll really appreciate your help.
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

6 Replies

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

    manup07 

    can you please explain the logic of what you want to achieve.

    i mean why you need this dax measure . 

     

    why you are using generate  ? 

     

    can you please clarfiy more ? 

    or can you share your file so i can take a look .  

     

     

    best regards,

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