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
     
mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

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's avatar
Glyndwr
Regular Visitor
5 years ago

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's avatar
    Glyndwr
    Regular Visitor
    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