Forum Discussion

Glyndwr's avatar
Glyndwr
Regular Visitor
5 years ago
Solved

Table Total is not correct for a Measure column

I have created a Measure as: Hours = sum('rdowner V_ACTIVITY'[Duration]) * sum('rdowner V_ACTIVITY'[NumberOfOccurrences]) The Total for this column is incorrect when there are more than one rows r...
Need Help
  • Glyndwr's avatar
    Glyndwr
    5 years ago

    I have been experimenting and have changed from Measure to Column using:

    Hours = 'rdowner V_ACTIVITY'[Duration] * 'rdowner V_ACTIVITY'[NumberOfOccurrences]
    This seems to work if only one row is filtered or more rows. 
    I have been viewing introductory video by Avi Singh (https://www.youtube.com/watch?v=AGrl-H87pRU). In this he recomends using Meaure instead of Column. What is the general concensus on this please?
     
    Kind regards,
    Glyn
     
Glyndwr's avatar
Glyndwr
Regular Visitor
5 years ago

HASONEFILTER not work. Thanks for your efforts.

 

Kind regards,

Glyn

 

  • v-cazheng-msft's avatar
    v-cazheng-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, Glyndwr 

    To get the right Total, you can try the following solutions.

     

    Solution 1  Create a Calculated column

    Hours_col =

    CALCULATE (

        SUM ( 'rdowner V_ACTIVITY'[Duration ] )

            * SUM ( 'rdowner V_ACTIVITY'[Number of Occurrences] ),

        VALUES ( 'rdowner V_ACTIVITY'[item] )

    )

     

    Solution 2 Create two Measures

    Hours_without_total =

    SELECTEDVALUE ( 'rdowner V_ACTIVITY'[Duration ] )

        * SELECTEDVALUE ( 'rdowner V_ACTIVITY'[Number of Occurrences] )

     

    Hours =

    IF (

        HASONEFILTER ( 'rdowner V_ACTIVITY'[item] ),

        [Hours_without_total],

        SUMX ( 'rdowner V_ACTIVITY', [Hours_without_total] )

    )

     

    The result looks like this:

    Best Regards,

    Caiyun Zheng

     

    Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.