Forum Discussion

ScottA's avatar
ScottA
Frequent Visitor
6 years ago
Solved

Calculating totals based on data subsets

Hi All,   I have versioned roster data, and I get this by employee. E.g. Everyones roster is published (at differing times) and the data flows through. If a change is then made to someones roster, ...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi ScottA ,

     

    We can use the following measure by using SUMX function to meet your requirement.

     

    *Hotel Nights =

SUMX (

    VALUES('Roster Data'[Employee Number]),

    VAR LatestRosterVersion = [*Latest Roster Version]

    RETURN

        CALCULATE (

            COUNTROWS ( 'Roster Data' ),

            'Roster Data'[Layover Location] <> "",

            'Roster Data'[Roster Version] = LatestRosterVersion

        )

)


    And here is the result:

     

     

    If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

     

v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

Hi ScottA ,

 

We can use the following measure by using SUMX function to meet your requirement.

 

*Hotel Nights =

SUMX (

    VALUES('Roster Data'[Employee Number]),

    VAR LatestRosterVersion = [*Latest Roster Version]

    RETURN

        CALCULATE (

            COUNTROWS ( 'Roster Data' ),

            'Roster Data'[Layover Location] <> "",

            'Roster Data'[Roster Version] = LatestRosterVersion

        )

)


And here is the result:

 

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

 

ScottA's avatar
ScottA
Frequent Visitor
6 years ago

Hi v-lid-msft 

 

This is perfect, thank you. I can see how VALUES() is creating a distinct list/table of employee_numbers, and then SUMX is allowing the original measure to SUM for each row of that table. 

 

I had been experimenting with SUMX but I just couldn't visualise how to get it to work.

 

Really appreciate it, cheers