Forum Discussion
SUM unique values from filtered table
Hello, everyone! I am new in Power BI, and I haven't been able to start my dataviz because I'm stuck with the data transformation.
I have a data set that is essentially this.
I have a "nested" product list with "repeated" information about the stores. If you see, I have different stores in each country, but the values from Store Class and Employees are the same information for the same store ID.
The Store ID is unique, regardless of the country.
The Store Class is not unique.
What I need to calculate is the total number of employees in different scenarios.
- Total employees per country
- Total employees per store class per country
For example, I manually calculated the values from Luxembourg in Excel and this is how I would want to calculate the values automatically in Power BI
Total employees per country: 98
Total employees per country per store class:
- Class A: 50
- Class B: 25
- Class C: 3
- Class 😧 20
I am trying to calculate this as either another column and this would give me repeated values. Or as a measure, that I can then use in my data visualization as a Card with a filter.
Any help will be much appreciated!
5 Replies
smpa01Community Champion
Anonymous try this
- smpa01
This is the sample table I have 😄
Country Store ID Store Class Employees Products Total employees per country Total employees per country per store class Luxembourg 12 A 5 Rice 98 50 Luxembourg 12 A 5 Beans 98 50 Luxembourg 543 B 25 Cucumber 98 25 Luxembourg 543 B 25 Rice 98 25 Luxembourg 543 B 25 Beans 98 25 Luxembourg 4757 A 45 Rice 98 50 Luxembourg 4757 A 45 Tomatoes 98 50 Luxembourg 23 C 3 Tomatoes 98 3 Luxembourg 23 C 3 Beans 98 3 Luxembourg 23 C 3 Chicken 98 3 Luxembourg 224 D 20 Beans 98 20 Luxembourg 224 D 20 Pasta 98 20 Germany 357 A 32 Cucumber Germany 357 A 32 Rice Germany 357 A 32 Beans Germany 2356 A 6 Rice Germany 2356 A 6 Tomatoes Germany 2356 A 6 Chicken Germany 2356 A 6 Beans Germany 9 D 8 Rice Germany 9 D 8 Tomatoes Germany 9 D 8 Chicken Germany 9 D 8 Beans Germany 9 D 8 Cucumber Germany 9 D 8 Pasta Germany 97 B 54 Lemon Germany 97 B 54 Beans Germany 97 B 54 Cucumber France 23 C 5 Cucumber France 23 C 5 Beans France 23 C 5 Lemon France 23 C 5 Pasta France 568 A 23 Carrots France 568 A 23 Chicken France 568 A 23 Pasta France 568 A 23 Lemon France 568 A 23 Coriander France 568 A 23 Beans France 67 A 9 Chicken France 67 A 9 Pasta France 67 A 9 Carrots France 3267 D 49 Chicken France 3267 D 49 Pasta France 3267 D 49 Lemon France 3267 D 49 Coriander France 3267 D 49 Beans France 111 A 27 Chicken France 111 A 27 Pasta France 111 A 27 Lemon
Hi smpa01 this helps for this simple data set, indeed.
The problem is that my actual dataset has thousands of "Countries" (I know there are not thousands of countries, but I changed the field names to share the dataset structure haha)
I was hoping to get a way to calculated with DAX measures so that I can then add Card visualizations with a slicer to select the Country I want to see or the class of store I want to see or both.
Do you know any solution for this?
Thanks a lot!
- v-easonf-msft
Community Support
Hi, Anonymous
It looks like [base] measure created by smpa01 can meet your needs. I didn't find any error in his measure. The card can be filtered properly. Please recheck it.
 
If I have misunderstood, please explain your needs in more detail.
Best Regards,
Community Support Team _ Eason
