SUM unique values from filtered table

[Anonymous]

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!  

[Anonymous]

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]

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

[smpa01]

@Anonymous  try this

[smpa01]

@Anonymous https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523#M607150

[Anonymous]

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