Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Sum total not matching

Jump to Best Answer
  • 1.  Sum total not matching

    Gold Contributor
    Posted Jul 22, 2021 06:00 AM
      |   view attached
    Hi, Attached is the PBIX file. In the file the "Post contract volume" total is not matching, could anyone help pls

    The actual post contract volume is only 59,211 and not what is displayed in the file

    Thanks in advance



    ------------------------------
    FA
    ------------------------------

    Attachment(s)

    pbix
    C Tracker.pbix   208 KB 1 version


  • 2.  RE: Sum total not matching

    Top Contributor
    Posted Jul 23, 2021 07:08 AM
    The "built in" totals row of table/matrix visuals is probably the worst thing about how power bi works. I forget the exact details, but it has something to do with filters. If you put the whole dataset in the table unfiltered, does the total show correctly?

    ------------------------------
    Sam Duval
    Data Quality Analyst
    Element Financial
    Indianapolis IN
    ------------------------------



  • 3.  RE: Sum total not matching

    Top Contributor
    Posted Jul 26, 2021 08:54 AM
    I believe the problem is with your filters. You are filtering the value in the measure, but the tablix total row is calculating the sum from the table as filtered by the slicers in the page. The total row doesn't always show you the true sum of what is listed in the visual. The totals row really doesn't work with more complex relationships like you have defined here. Perhaps someone more well versed in PBI relationship building can offer advice as to how to structure your data to work with what you are trying to do, but as is today, you're total row will never match the actual values because they are totaling two separate things.

    ------------------------------
    Sam Duval
    Data Quality Analyst
    Element Financial
    Indianapolis IN
    ------------------------------



  • 4.  RE: Sum total not matching
    Best Answer

    Top Contributor
    Posted Jul 26, 2021 03:39 PM
      |   view attached
    Hi Vinu:

    I think SUMX can get tricky for the grand total line as no such Data table filtering is provided by the Grand Total row.  Your report shows correctly on the individual lines. I just summed up your activities and used this measure below and have attached your file with this included. Essentially you needed to add "ALL" so the entire Activity tabled would be filtered. I hope this helps. Bill

    PCVOL3 = CALCULATE([Activity Volume],
    FILTER(ALL(
    'Activity'),
    'Activity'[Date] >=MIN('Tracking Benifits'[Endofmonth])
    && 'Activity'[Date] <=MIN('Tracking Benifits'[End Date])
    )
    )


    ------------------------------
    William Skelley
    01106
    Longmeadow MA
    8602807221
    ------------------------------

    Attachment(s)