I am trying to create a table where I could compare the performance of different countries in terms of sales. I have a list of countries and a list of different products and subproducts. My plan is to have a table with the following columns:
1- Subregion, where I would have the set of countries that are part of this subregion (A,B,C,...)
2- Percentage of sales of each subregion. Then I would filter for a product and would see the percentage that any product has in terms of the total revenue of each country. So, if I were to filter for product X, I would see that country A has 10% of its revenue coming from it, while B has 85%, C 30%...
I've been trying to use calculate(sum(Sales[Total])/calculate(sum(Sales[total],ALL(Sales[Subregion]), but that way I only get the % that each country represents in the subregion group, and if I filter for any product, I get the % that every country represents for any given product for that area group (always sum 1).
Can anyone help me figure out how to solve this?
in the paragraph you describe the table you are looking for, but I have to admit that I do not understand where the product comes in to play that you want to use to filter your table.
Maybe it would be helpful to create a little pbix file with sample data, upload this file to onedrive or dropbox and share the link.