Remove groups and keep values

[AmiraBedh]

I have a simple table where I put Department, Number of Employees and Salary Expenses like below :

DeptID  Number_of_Employees Salary_Expenses

1                   159                                 100000

2                   240                                 135000

3                     55                                  78999

The DeptID is coming from the Department table

The Number_of_Employees is a measure based on the fact table HR_Anatlytics :

Number_of_Employees := DISTINCTCOUNT(HR_Anatlytics[EmployeeSK])

The Salary_Expenses is a measure based on the fact table HR_Anatlytics :

Salary_Expenses := SUM(HR_Anatlytics[Salary])

The relationship between the Department dimension and the HR_Anatlytics is one to many.

My expected output is like below, I want to remove the groups and keep values

 Number_of_Employees Salary_Expenses

 159                                 100000

 240                                 135000

55                                  78999

[smpa01]

@AmiraBedh  Number_of_Employees and Salary_Expenses are both measures. In DAX Measures are calculated based on what is available in the filter context. If the axis/filter context is removed, the measures will not evaluate.

The workaround is to create equivalent calculated columns, cause they can work as axis.

[AmiraBedh]

@smpa01  I tried to create a calculated column like below : 

Salary_Expenses := SUM(HR_Anatlytics[Salary])

but it is not working 

[smpa01]

@AmiraBedh  It should not work because you are using the same Measure expression for the calculated column and hoping it returns the same. Wrap this in a CALCULATE

Salary_Expenses= CALCULATE(SUM(HR_Anatlytics[Salary]))

[AmiraBedh]

Still not working. It is giving the same value for all the columns.

[Anonymous · Accepted solution]

Hi @AmiraBedh,

Current power bi does not support using measure expressions as category/axis fields to expand other measure calculations. (measure expressions are calculated based on the row contexts so it required a category/group field to define the calculate ranges)

In my opinion, I think you need to use calculate column instead:

Number_of_Employees :=
CALCULATE (
    DISTINCTCOUNT ( HR_Anatlytics[EmployeeSK] ),
    FILTER ( HR_Anatlytics, [DeptID] = EARLIER ( HR_Anatlytics[DeptID] ) )
)

In addition, you can also try to create a new calculated table that used summary records:

NewTable =
SELECTCOLUMNS (
    SUMMARIZE (
        HR_Anatlytics,
        [DeptID],
        "Number_of_Employees", DISTINCTCOUNT ( HR_Anatlytics[EmployeeSK] ),
        "Salary_Expenses", SUM ( HR_Anatlytics[Salary] )
    ),
    "Number_of_Employees", [Number_of_Employees],
    "Salary_Expenses", [Salary_Expenses]
)

If the above formulas do not help, can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us quickly clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng