Forum Discussion
Calculating rows in Power Bi
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
2 Replies
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.
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,
