Forum Discussion

dokat's avatar
dokat
Icon for Post Prodigy rankPost Prodigy
4 years ago
Solved

Add calculated row to in table

Hi,

 

I have below matrix table that i'd like to add a calculated row (highlighted in red) on the bottom of it.

I have calculated measures for 2020 and 2021 (Returns/Gross Sales)  but not sure how to add to the table.

 

Can any help with the formula?

 

P&L20222021Chg
Gross Sales                 300,396,404                 270,396,269                   30,000,135
Returns                   75,628,912                   60,752,682                   14,876,230
Net Sales                 224,767,492                 209,643,587                   15,123,905
Returns/Gross Sales25%22%                                      3
Need Help
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, dokat 

     

    You can try the following methods.

    Measure =
SWITCH (TRUE (),
    HASONEVALUE ( 'Table'[P&L] ) && HASONEVALUE ( 'Table'[Year] ),
        CALCULATE ( SUM ( 'Table'[Value] ),
            FILTER (  ALL ( 'Table' ),
                [Year] = SELECTEDVALUE ( 'Table'[Year] ) && [P&L] = MAX ( 'Table'[P&L] )  ) ),
    HASONEVALUE ( 'Table'[P&L] ) && NOT ( HASONEVALUE ( 'Table'[Year] ) ),
        CALCULATE (   MAX ( 'Table'[Value] ),
            FILTER ( 'Table', [Year] = 2022 && [P&L] = MAX ( 'Table'[P&L] ) ) )
            - CALCULATE ( MAX ( 'Table'[Value] ),
                FILTER ( 'Table', [Year] = 2021 && [P&L] = MAX ( 'Table'[P&L] ) ) ),
    HASONEVALUE ( 'Table'[Year] ) && NOT ( HASONEVALUE ( 'Table'[P&L] ) ),
        FORMAT ( DIVIDE ( CALCULATE ( SUM ( 'Table'[Value] ),
                    FILTER ( ALL ( 'Table' ),
                        [Year] = SELECTEDVALUE ( 'Table'[Year] )&& [P&L] = "Returns" )  ),
                CALCULATE ( SUM ( 'Table'[Value] ),
                    FILTER ( ALL ( 'Table' ),
                        [Year] = SELECTEDVALUE ( 'Table'[Year] )  && [P&L] = "Gross Sales" ) ) ),
            "#%" ),
    CALCULATE ( COUNT ( 'Table'[P&L] ),
        FILTER ( ALL ( 'Table' ), [Year] = MAX ( 'Table'[Year] ) ) )
)

    You can change the names of rows total and columns total to what you need.

     

    Best Regards,

    Community Support Team _Charlotte

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

5 Replies

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, dokat 

     

    You can try the following methods.

    Measure =
SWITCH (TRUE (),
    HASONEVALUE ( 'Table'[P&L] ) && HASONEVALUE ( 'Table'[Year] ),
        CALCULATE ( SUM ( 'Table'[Value] ),
            FILTER (  ALL ( 'Table' ),
                [Year] = SELECTEDVALUE ( 'Table'[Year] ) && [P&L] = MAX ( 'Table'[P&L] )  ) ),
    HASONEVALUE ( 'Table'[P&L] ) && NOT ( HASONEVALUE ( 'Table'[Year] ) ),
        CALCULATE (   MAX ( 'Table'[Value] ),
            FILTER ( 'Table', [Year] = 2022 && [P&L] = MAX ( 'Table'[P&L] ) ) )
            - CALCULATE ( MAX ( 'Table'[Value] ),
                FILTER ( 'Table', [Year] = 2021 && [P&L] = MAX ( 'Table'[P&L] ) ) ),
    HASONEVALUE ( 'Table'[Year] ) && NOT ( HASONEVALUE ( 'Table'[P&L] ) ),
        FORMAT ( DIVIDE ( CALCULATE ( SUM ( 'Table'[Value] ),
                    FILTER ( ALL ( 'Table' ),
                        [Year] = SELECTEDVALUE ( 'Table'[Year] )&& [P&L] = "Returns" )  ),
                CALCULATE ( SUM ( 'Table'[Value] ),
                    FILTER ( ALL ( 'Table' ),
                        [Year] = SELECTEDVALUE ( 'Table'[Year] )  && [P&L] = "Gross Sales" ) ) ),
            "#%" ),
    CALCULATE ( COUNT ( 'Table'[P&L] ),
        FILTER ( ALL ( 'Table' ), [Year] = MAX ( 'Table'[Year] ) ) )
)

    You can change the names of rows total and columns total to what you need.

     

    Best Regards,

    Community Support Team _Charlotte

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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    If Returns and Gross sales are measures, then write this measure

    Returns (%) = divide([returns],[gross sales])

    • dokat's avatar
      dokat
      Icon for Post Prodigy rankPost Prodigy
      4 years ago

      I have formula that calculates the % but dont know how can i add as a row to the bottom of the table?

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        4 years ago

        If Returns and Gross sales are measures, then just as you would have dragged those to the visual, drag this new measure as well to the visual.