Wrong Column Subtotals

[manup07]

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.

[v-yangliu-msft · Accepted solution]

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

[v-yangliu-msft · Accepted solution]

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 · Accepted solution]

Thanks @v-yangliu-msft , 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

[manup07]

Here is the context of the measure: https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/DAX-to-get-number-of-nights-per-mont...

[Daniel29195]

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

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.

[Daniel29195]

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