Forum Discussion

AmiraBedh's avatar
AmiraBedh
Icon for Super User rankSuper User
4 years ago
Solved

Remove groups and keep values

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                            ...
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

AmiraBedh's avatar
AmiraBedh
Icon for Super User rankSuper User
4 years ago

smpa01  I tried to create a calculated column like below : 

Salary_Expenses := SUM(HR_Anatlytics[Salary])

but it is not working 

smpa01's avatar
smpa01
Icon for Community Champion rankCommunity Champion
4 years ago

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's avatar
    AmiraBedh
    Icon for Super User rankSuper User
    4 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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