Forum Discussion

FOXYBARK's avatar
FOXYBARK
Helper III
1 year ago
Solved

Summing distinct values based on grouping

HI. 

I have the below sample data set. 

I want to have a measure that sums [Head Count] by [Floor]. For example, the 5th floor total head count would be 57. 6th floor head count would be 87. I have many floors and many depts within each floor. DAX syntax please. Thank you. 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    1 year ago

    Hi,

    I am not sure how your expected result looks like, but if you are trying to create a measure in the table visualization with having floor name as the first column, please try to create a measure somehting like below, and then put it into the visualization.

     

    expected result measure: =
SUMX (
    SUMMARIZE (
        TableName,
        TableName[Floor],
        TableName[Floor Name],
        TableName[FloorDeptKey],
        TableName[Head Count]
    ),
    TableName[Head Count]
)

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    1 year ago

    Hi,

    I am not sure how your expected result looks like, but if you are trying to create a measure in the table visualization with having floor name as the first column, please try to create a measure somehting like below, and then put it into the visualization.

     

    expected result measure: =
SUMX (
    SUMMARIZE (
        TableName,
        TableName[Floor],
        TableName[Floor Name],
        TableName[FloorDeptKey],
        TableName[Head Count]
    ),
    TableName[Head Count]
)