Forum Discussion
Table Total is not correct for a Measure column
I have created a Measure as:
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
9 Replies
HI Glyndwr
Can you share your PBIX.
Sorry my institution does not seem to allow this.
Kind reagrds,
Glyn
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
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
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
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.
HASONEFILTER not work. Thanks for your efforts.
Kind regards,
Glyn
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.
try:
New measure = SUMX( rdowner V_ ACTIVITY, sum('rdowner V_ACTIVITY'[Duration]) * sum('rdowner V_ACTIVITY'[NumberOfOccurrences]))
