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.