Table Total is not correct for a Measure column

[Glyndwr]

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 returned for the filter:

One row

How can I get the correct Total for the Hours column please?

[PaulDBrown]

@Glyndwr 

try:

New measure = SUMX( rdowner V_ ACTIVITY, sum('rdowner V_ACTIVITY'[Duration]) * sum('rdowner V_ACTIVITY'[NumberOfOccurrences]))

[v-cazheng-msft]

Hi, @Glyndwr 

You can try to use HASONEFILTER function to help you deal with Measure totals issue.

For more details, you can refer Dealing with Measure Totals and Measure Totals, The Final Word.

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.

[Glyndwr]

HASONEFILTER not work. Thanks for your efforts.

Kind regards,

Glyn

[v-cazheng-msft]

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.

[mahoneypat]

You can use the pattern shown in this video to get the correct totals.

DAX Fridays! #25: Wrong Grand Totals in Power BI - YouTube

Basically, you will use a pattern like

CorrectTotalMeasure = SUMX(VALUES(Table[Column]), [YourMeasure])

or

CorrectTotalMultipleColumns = SUMX(SUMMARIZE(Table, Table[Column1], Table[Column2]), [YourMeasure])

Pat

[Glyndwr]

Hi Pat,

I followed the video and changed my Measure to:

Hours = if(HASONEVALUE('rdowner V_ACTIVITY'[Activity]), sum('rdowner V_ACTIVITY'[Duration]) * sum('rdowner V_ACTIVITY'[NumberOfOccurrences]), SUMX(VALUES('rdowner V_ACTIVITY'[Activity]), sum('rdowner V_ACTIVITY'[Duration]) * sum('rdowner V_ACTIVITY'[NumberOfOccurrences])))

 However, this was taking for ever to calculate. So before I got a result I changed to Column with:

Hours = if(HASONEVALUE('rdowner V_ACTIVITY'[Activity]), 'rdowner V_ACTIVITY'[Duration] * 'rdowner V_ACTIVITY'[NumberOfOccurrences], SUMX(VALUES('rdowner V_ACTIVITY'[Activity]), 'rdowner V_ACTIVITY'[Duration] * 'rdowner V_ACTIVITY'[NumberOfOccurrences]))

 This gives me a value for Hours of:

• 545792 when filtered to a single row
• 545792 and 136448 when filtered for two rows

Based on these Hours the Total is now correct; however the values for Hours are obviously not correct.

Kind regards,

Glyn

[Glyndwr · Accepted solution]

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

[Anonymous]

HI @Glyndwr 

Can you share your PBIX. 

[Glyndwr]

Sorry my institution does not seem to allow this.

Kind reagrds,

Glyn