Calculating rows in Power Bi

[nsethia]

Hi all,

I've separate spreadsheets for each store with data similar to

Store A                    Store B

Product   Sales         Product    Sales

Shoes     100            Shoes      150

Shirts       50            Shirts         70      

I need to add up total sales in a new column like 

Store A                    Store B

Product   Sales         Product    Sales      Total Sales

Shoes     100            Shoes      150                250

Shirts       50            Shirts         70                120

Is there a way to do that in Powerbi desktop?

Thanks

[v-sihou-msft]

@nsethia

In this scenario, as you already separate tables for each store into different worksheet, you can generate one dataset for each worksheet. Since each dataset has same structure, you can use "merge queries" to combine two tables together. Just rename columns into "Sales for Store A" and "Sales for Store B".

Then you can easily create a calculated column to calculate the total.

Regards,

[Phil_Seamark]

I take it you have more than 2 stores.  

I recommend you append the tables to each other.  This can be done in the Query Editor using the APPEND function in the ribbon, or you can do this in DAX using the UNION fuction.

You are after a single table that looks like this

Store    Product Sales
------   ------- ,-------
Store A, Shoes , 100
Store A, Shirts ,50
Store B, Shoes , 150
Store B, Shirts,   70

Once you have your data in this format, you can use the various grid visuals (matrix) to show the data.