add calculated row to table visualization that sums certain rows

[Anonymous]

Hi all,

I current have a sample data visualisation in a table format from Beef to Watermelon. What I wish to do is create a new calculated row "Fruits" which will sum the values from Apple to Watermelon. I understand that it is possible to create a measure for column but what about for rows? Any help is appreciated. Thanks!

Food    Total No. of Sales       Total No. of Sales in Morning             % of Food sold in Morning
Beef                  560                           125                                                           22.32%
Apple               146                            211                                                          4.38%
Banana             238                            54                                                            22.69%
Pineapple         520                           122                                                           23.46%
Watermelon     501                           125                                                           24.95%
Fruits               1405                          322                                                           22.91%

[rajendraongole1 · Accepted solution]

Hi @Anonymous - You can create a new table or calculated table that includes the existing data and the calculated totals 

In ROW function i have used the sum of Total no. of sales inside the formaule, you can also create seperate measure and reuse it in summary table.

Use below calculated table :

SummaryTable =

UNION(

    SELECTCOLUMNS(

        'Fruits',

        "Food", 'Fruits'[Food],

        "Total No. of Sales", 'Fruits'[Total No. of Sales],

        "Total No. of Sales in Morning", 'Fruits'[ Total No. of Sales in Morning],

        "% of Food sold in Morning", 'Fruits'[% of Food sold in Morning]

    ),

    ROW(

        "Food", "Fruits",

        "Total No. of Sales", SUM(Fruits[Total No. of Sales]),

        "Total No. of Sales in Morning", SUM(Fruits[ Total No. of Sales in Morning]),

        "% of Food sold in Morning", DIVIDE(SUM(Fruits[ Total No. of Sales in Morning]),SUM(Fruits[Total No. of Sales]))

    )

)

Hope it works

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!