measure total (using the summarize technique) does not match sum of rows in table

[Anonymous]

I have the following measures:

Total UT Hours Remaining - driven by fcst pd slicer - for table by mbr and pd = 
VAR __table =
SUMMARIZE(
    'Dimension Member Weekly Schedule'
    ,'Dimension Member Weekly Schedule'[Mbr Name - Mbr Nbr]
    ,'Dimension Member Weekly Schedule'[Period Label]
    ,"__value"
    ,[Total UT Hours Remaining - driven by fcst pd slicer]
)

RETURN
    SUMX(
        __table
        ,[__value]
    )

Total UT Hours Remaining - driven by fcst pd slicer = 
VAR FcstPdSlicer_Pd = SELECTEDVALUE('Fcst Period Slicer'[Period Key])

RETURN
    CALCULATE(
        SUM('Dimension Member Weekly Schedule'[Mbr Weekly Adjusted Target Hours]) + 0
        ,'Dimension Member Weekly Schedule'[Period Key - Virtual] = FcstPdSlicer_Pd
        ,'Dimension Member Weekly Schedule'[Mbr Current Row Indicator] = "Current"
        ,'Dimension Member Weekly Schedule'[Week Relative Label] = "Current Wk" || 'Dimension Member Weekly Schedule'[Week Relative Label] = "Future Wk"
    )

I have measure [Total UT Hours Remaining - driven by fcst pd slicer - for table by mbr and pd] on a table visual with dimensions 'Dimension Member Weekly Schedule'[Mbr Name - Mbr Nbr] and 'Dimension Member Weekly Schedule'[Period Label] (i.e., the "grain" of the table visual matches the grain of the SUMMARIZE function in my measure definition).  However, the value for this measure on the Total row does not equal the sum of the values for this measure from the detail rows (determined by exporting the table visual and summing the detail row values in Excel).  I have followed the pattern described here, which I thought would make the Total row value equal the sum of the detail row values:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907 

To identify the root cause, I also included measure [Total UT Hours Remaining - driven by fcst pd slicer] on the table visual.  It returns the same detail row values and Total row value as the other measure.  So, there must be something wrong in my "for table by mbr and pd" measure.  How can I resolve my Total value problem?  If it matters, I have a few fields in my filter pane at the page level with some default selections applied.

[mahoneypat]

Totals in Power BI tables/matrices are not really totals.  It is calculating your measure w/o the filters that the rows above provide.  It can be solved with this pattern.

NewMeasure = SUMX(Values(Table[ColumnUsedInYourVisual), [YourMeasure])

This measure will calculate correctly for each row and for your totals.

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

@mahoneypat 
I already know that the Total "row" does not have the same filter context that the detail rows do.  That is why I followed the solution pattern in the link that I posted.  Your solution won't work for me since I have 2+ columns in my table visual.  (The more general solution is virtually the same as your solution, but with a SUMMARIZE first.)

[mahoneypat]

Sorry.  Didn't mean to offend.  To clarify, I was proposing you keep your existing measure and then use it in a SUMX(Values()...  But since you have 2+ columns, you could use it in a SUMX(SUMMARIZE

Would an approach like this work?

NewMeasure = SUMX(SUMMARIZE(Table, Table[Col1InVisual], Table[Col2InVisual), [YourOriginalMeasure])

Sorry I didn't use the original table and column names.  The original post is not visible in the reply window.

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Actually, as shown in my original post, that is precisely the solution method I have used.

I was able to identify the root cause of my issue.  It turns out that 1) I don't need to use the "measure of a measure" solution method (i.e., the base measure computes correctly for both the detail rows and the Total row) and 2) measure formatting precision has a big impact in determining the correctness of my measure for the Total row.  I used "Decimal" formatting (and, more importantly, 1 decimal place) in both my base measure and my "SUMMARIZE" measure.  Both returned the same Total value in PBI as well as in Excel (even though the values between PBI and Excel didn't match).  This is how I knew that my "measure of a measure" solution method wasn't necessary.  When I changed the formatting on my "SUMMARIZE" measure to include more than 1 decimal place (actually, "General" and "Auto") and re-tested in Excel, Excel's value matched PBI's value.  This is because more decimal places were kept in this export to Excel than in the previous export to Excel.  So, it seems that both the detail rows and the Total row are computed using the full decimal precision and then rounded, which means that, if you use a low precision (e.g., 1 decimal place), then the Total row still won't perfectly match the sum of the detail rows, even if you use the "measure of a measure" solution method and "Auto" precision in the base measure.

[Ashish_Mathur]

Hi,

Share a simple dataset and show the expected result.