Forum Discussion

mp390988's avatar
mp390988
Icon for Post Partisan rankPost Partisan
3 months ago
Solved

SUMMARISECOLUMNS ISSUE

Hello,

I am trying to create a summary table for revenue per year per month and the revenue per year.
I created the DAX table using the below code.

s1 = 
SUMMARIZECOLUMNS(
    DimCalendar[Year],
    DimCalendar[Month],
    FILTER(
        FactMain,
        FactMain[ColumnType] = "Revenue"
    ),
    "Revenue", [Total Rev],
    "Revenue Per Year",
        CALCULATE(
            [Total Rev],
            ALLEXCEPT(DimCalendar,DimCalendar[Year])
        )
)


But the issue I am having is that my "Revenue Per Year" field is making no sense as it is just simply returning the individual revenues when it should be returning the total revenue for that year. 


Any idea why this is happening?

Thank You!



 

 

Need Help
Show and Tell

14 Replies

  • mp390988's avatar
    mp390988
    Icon for Post Partisan rankPost Partisan
    3 months ago

    Hi VijayP ,

    It is still not working.

     

    This is my semantic model, nothing extravagent going on here.
    All I can think of is that the DimCalendar[Month] is sorted by DimCalendar[MonthNumber].

     

    Thank You!

    • oussamahaimoud's avatar
      oussamahaimoud
      Icon for Memorable Member rankMemorable Member
      3 months ago

      mp390988,

      You need to remove both the Month and MonthNumber filters explicitly:

       

      s1 = 

      SUMMARIZECOLUMNS(

          DimCalendar[Year],

          DimCalendar[Month],

          "Revenue", [Total Rev Revenue],

          "Revenue Per Year",

              CALCULATE(

                  [Total Rev Revenue],

                  ALL(DimCalendar[Month], DimCalendar[MonthNumber])

              )

      )

       

      Or even safer, just wipe the entire calendar filter and restore only Year:

       

      s1 = 

      SUMMARIZECOLUMNS(

          DimCalendar[Year],

          DimCalendar[Month],

          "Revenue", [Total Rev Revenue],

          "Revenue Per Year",

              CALCULATE(

                  [Total Rev Revenue],

                  ALL(DimCalendar),

                  VALUES(DimCalendar[Year])

              )

      )

      • mp390988's avatar
        mp390988
        Icon for Post Partisan rankPost Partisan
        3 months ago

        Hi oussamahaimoud ,

        Sorry to bother you again but I was thinking with the following formula it is bad practice right to use the filter the way it has been done? What is the best way to write this using good practice please?


        AllClients = SUMMARIZECOLUMNS(
    FactMain[TDMonth],
    FactMain[TD Month Formatted],
    FactMain[TradeDate],
    FactMain[DealerGroup],
    FactMain[DealerID],
    FactMain[ClientID],
    FactMain[ClientName],
    FactMain[Product],
    FILTER(
        FactMain,
        FactMain[ColumnType] = "Revenue" && ISBLANK(FactMain[TDMonth])=FALSE()
    ),
    "Rev", sum(FactMain[GBPRevenue]),
    "Vol", sum(FactMain[GBPVolume])
)

         

        Thank You,

        M

  • oussamahaimoud's avatar
    oussamahaimoud
    Icon for Memorable Member rankMemorable Member
    3 months ago

    Hi mp390988,

    Hope you're doing well!

     

    SITUATION :

    The problem is that ALLEXCEPT inside SUMMARIZECOLUMNS doesn't behave as expected because SUMMARIZECOLUMNS already applies its own filter context for each row (both Year AND Month). The ALLEXCEPT tries to remove the Month filter, but the grouping context of SUMMARIZECOLUMNS overrides it, so it ends up returning the same value as the monthly Revenue.

     

    SOLUTION :

    I suggest to replace ALLEXCEPT with ALL(DimCalendar) combined with VALUES to reintroduce only the Year filter:

     

    s1 = 
    SUMMARIZECOLUMNS(
    DimCalendar[Year],
    DimCalendar[Month],
    FILTER(
    FactMain,
    FactMain[ColumnType] = "Revenue"
    ),
    "Revenue", [Total Rev],
    "Revenue Per Year",
    CALCULATE(
    [Total Rev],
    ALL(DimCalendar[Month])
    )
    )

     

    So, by using ALL(DimCalendar[Month]), you are explicitly removing the Month filter while keeping the Year grouping intact, which forces the calculation to aggregate across all months for that year.

     

    Approach What happening?

    ALLEXCEPT(DimCalendar, DimCalendar[Year])Gets overridden by SUMMARIZECOLUMNS row context → returns monthly value
    ALL(DimCalendar[Month])Explicitly clears only the Month filter → correctly sums all months per Year

     

    If your [Total Rev] measure itself involves complex filters, you may also consider creating the Revenue Per Year as a separate measure and referencing it in the table, as measures tend to handle filter context more predictably than inline CALCULATE expressions inside SUMMERIZECOLUMNS.

     

    Hope this helps. Feel free to ask me questions if needed, and don’t forget to like (kudos) & Accept as Solution if this guidance worked for you. That's motivate me to keep helping.

     

    Best regards,

    Oussama (Data Consultant & Fabric's Expert)

  • mp390988's avatar
    mp390988
    Icon for Post Partisan rankPost Partisan
    3 months ago

    Hi oussamahaimoud ,

    Thank you for your reply.

     

    Unfortunately, it still hasn't fixed the issue although I tried your suggested approaches. 

     

    Approach 1

    s1 = 
SUMMARIZECOLUMNS(
    DimCalendar[Year],
    DimCalendar[Month],
    FILTER(
        FactMain,
        FactMain[ColumnType] = "Revenue"
    ),
    "Revenue", [Total Rev],
    "Revenue Per Year",
        CALCULATE(
            [Total Rev],
            ALL(DimCalendar),
            VALUES(DimCalendar[Year])
        )
)

     

    Approach 2

    s1 = 
SUMMARIZECOLUMNS(
    DimCalendar[Year],
    DimCalendar[Month],
    FILTER(
        FactMain,
        FactMain[ColumnType] = "Revenue"
    ),
    "Revenue", [Total Rev],
    "Revenue Per Year",
        CALCULATE(
            [Total Rev],
            ALL(DimCalendar[Month])
        )
)

     

     

    Thank You

    • oussamahaimoud's avatar
      oussamahaimoud
      Icon for Memorable Member rankMemorable Member
      3 months ago

      Ok!

       

      Move the FactMain filter into your base measure instead, and keep SUMMARIZECOLUMNS clean:

      Step 1 : Redefine your base measure (or create a new one):

       

      Total Rev Revenue = 

      CALCULATE(

          [Total Rev],

          FactMain[ColumnType] = "Revenue"

      )

       

      Step 2 : Rebuild the table without the FILTER argument:

       

      s1 = 

      SUMMARIZECOLUMNS(

          DimCalendar[Year],

          DimCalendar[Month],

          "Revenue", [Total Rev Revenue],

          "Revenue Per Year",

              CALCULATE(

                  [Total Rev Revenue],

                  ALL(DimCalendar[Month])

              )

      )

       

      REMEMBER ME: Never use FILTER(FactTable,...) directly inside SUMMARIZECOLUMNS when you also need context manipulation in the same table. Always push those filters into your measures, this is the golden rule for avoiding this exact class of bugs.

      • mp390988's avatar
        mp390988
        Icon for Post Partisan rankPost Partisan
        3 months ago

        Hi oussamahaimoud ,

         

        Thank you for your reply 🙂

         

        Your suggestion to remove the filter has now got it working as expected!!

         

         

        I do have one more question though, why is that row marked in the snapshot appearing? It has no year and no month?

         

        Thank You!

        #

         

        Thank You,

  • VijayP's avatar
    VijayP
    Icon for Community Champion rankCommunity Champion
    3 months ago

    mp390988  I have used a proper model and used the same measure 

    s1 = SUMMARIZECOLUMNS(
        Dates[Year],
        Dates[Month],
        FILTER(
          'Application',
           'Application'[Application] = "Excel"
        ),
        "Revenue", [Total Revenue],
        "Revenue Per Year",
            CALCULATE(
                [Total Revenue],
                ALLEXCEPT(Dates,Dates[Year])
            )
    )
    that is working fine! 
    My suggestion check your semantic model again!