Sum values in a measure by Group (returning a value and not a table \ column)

[Anonymous]

Hello experts!

I want to sum values in a column by a group (my grouping column exists in the same table with the column i want to sum). 

Grouping column - Employee_Group_Key

Column to sum - No_of_Deliveries_Remaining_for_current_month

Table - vwbi_target_calls

In my report I show calculations in matrix in two levels:

employee level (the highest level) - Dim_employees[ID_Full_EMP_Name]

customer level (the lowest level) - Dim_Customers[Customer_Name]

I created the following queries (non of them worked for me) - 

1. Sum No of Rem Deliveries (Group by Employee_Group_Key) =
SUMX(VALUES(vwbi_target_calls[Employee_Group_Key]),
CALCULATE(SUM(vwbi_target_calls[No_Of_Deliveries_Remaining_For_Current_Month])))

2. Sum No of Rem Deliveries (Group by Employee_Group_Key) =
CALCULATE(SUMX(VALUES(vwbi_target_calls[Employee_Group_Key]),[Total Remaining Deliveries (No Grouping)]))

***Total Remaining Deliveries (No Grouping) =

SUM(vwbi_target_calls[No_Of_Deliveries_Remaining_For_Current_Month])

The problem is that when i put the "Sum No of Rem Deliveries (Group by Employee_Group_Key)" measure in the matrix I see regular sum of values without grouping it - I understand why it happens so i put a filter in my report to filter the group (ID_Material_calssification_Group) but i get the same results.

This is the table where i want to sum the values

Each employee exists in one group but each customer can exists in different groups treated by different employess.

Do you know what can i do to solve it? 
Thanks in advance!

[v-jayw-msft]

Hi @Anonymous ,

I usually use below measures to calculate value by group:

measure1 = calculate(sum(value),filter(allexcept(table,category),filterexpression))

measure2 = calculate(sum(value),filter(all(table),category = selectedvalue(category)&&filterexpression))

Anyway, it's hard to write DAX formula without data.

So, please share some sample data and expected result to us if you don't have any Confidential Information.

Best Regards,

Jay

[Anonymous]

Hi !

But what if I don't have any filter expresion - only column (that i want to group by)? 

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

You could just remove it from the formula.

measure1 = calculate(sum(value),allexcept(table,category))

or

measure2 = calculate(sum(value),filter(all(table),category = selectedvalue(category)))

Best Regards,

Jay

[TomMartens]

Hey @Anonymous ,

please create a pbix that contains sample data but still reflects your data model, upload the pbix to onedrive or dropbox and share your link. if you are using Excel to create the sample data, share the xlsx as well.

Describe the expected outcome based on the sample data you provide.

Regards,

Tom