Forum Discussion

powerbinovice01's avatar
powerbinovice01
Frequent Visitor
2 years ago

Issues with SumX Function -- Table Totals Incorrect

Hello,
I have wated a handful of videos and played around with the Sum and SumX function.
I need assistance with calculating the correct totals for my measures within my table. 
The total difference betwen Weekly TW and LW Sales should be an estimate $4,000, but it is totaling the same amount to sum of Weekly Sales, $106,997. The WoW Growth % is also incorrect for other store locations as well, they all total to 100%, the average value should be about 53%. 

 

Here is my initial mesaure formula

Diff Between Weekly TW and LW =

(if(ISBLANK('Weekly Sales'[Weekly Netchange]),BLANK(),[Weekly NetChange]-[Last Week Sales]))
 
Here is one version of SumX I hae tried: 
Total Weekly Difference =
SUMX(
'Weekly Sales',
IF(
ISBLANK('Weekly Sales'[Weekly Netchange]) || ISBLANK('Weekly Sales'[Last Week Sales]),
BLANK(),
'Weekly Sales'[Weekly Netchange] - 'Weekly Sales'[Last Week Sales]
)
)
 
But the new total I recieve is ($198,799,000). The total sales does not surpass $171 Million. 

I am in need of a new learning opportunity.
Thank you! 

 

General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

8 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    2 years ago

    powerbinovice01 Nope, you have to SUMMARIZE the Weekly Sales table exactly as how it is summarized in your table visual and then do a SUMX across that table. First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

    This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

    Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
    https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

    Also: https://youtu.be/uXRriTN0cfY
    And: https://youtu.be/n4TYhF2ARe8

    • powerbinovice01's avatar
      powerbinovice01
      Frequent Visitor
      2 years ago

      Hello Greg,
      Thank you for your quick response. I can't believe I watched one of your videos earlier and you are the one to help me with a solution!
      Real qucik for clarification purposes, my weekly sales are currently summarized. Are you suggesting I create a new measure, something like Weekly Sales= (SumX('Weekly Sales' [Weekly Sales])
      Then proceed and create a new formula for my Difference between Current Week and Prior Week something along the lines of Diff TW and LW = SumX('Weekly Sales',
      IF(ISBLANK('Weekly Sales'[Weekly Netchange]) || ISBLANK('Weekly Sales'[Last Week Sales]),
      BLANK(),'Weekly Sales'[Weekly Netchange] - 'Weekly Sales'[Last Week Sales]))?


      Thank you for your clarification! 

      • foodd's avatar
        foodd
        Community Champion
        2 years ago

        There is no coincidence, as Greg is that awesome...like karma...the good kind.

  • powerbinovice01's avatar
    powerbinovice01
    Frequent Visitor
    2 years ago

    Hi Greg,
    Here is my current formula. I tested you suggested DAX query, but I had an error at the end referring to '__Result'


    For clarification, I am recycling Weekly NetChange from another formula I have written

    Here is what the measure looks like: WeeklyNetChange =sum('Weekly Sales'[Weekly Sales')

    Here is a sample of my data. I have used a star schema linking my vendor ID to my calendar table. I recieve weekly reports I am looking to subtrack the current week sales to the previous week sales based on report date.
    Additionally on the table I do not get the correct generated total for the Diff Current Year Weeks and Previous Year Week. 

     

    When formulating a similar table for MoM and YoY, the chart is able to filter and tally the correct totals. 

    Let me know if you would like more information.

    Thank you for your time and clarification.


    Kindly! 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi powerbinovice01  ,

      You can create another two new measure as below and put these new measures to replace the original measure

      [Diff Between Weekly TW and LW] and [Diff Weekly TYW and LYW] separately onto the table visual. Later check if they can return the correct total values...
      Measure 1 =
SUMX (
    GROUPBY (
        'Weekly Sales',
        'Weekly Sales'[Weekly Report Date],
        'Weekly Sales'[Vendor Name]
    ),
    [Diff Between Weekly TW and LW]
)
      Measure 2 =
SUMX (
    GROUPBY (
        'Weekly Sales',
        'Weekly Sales'[Weekly Report Date],
        'Weekly Sales'[Vendor Name]
    ),
    [Diff Weekly TYW and LYW]
)

       

      In addition, you can refer the following links to try to solve your problem...

      Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand

      Dax for Power BI: Fixing Incorrect Measure Totals

       

      If the above one can't help you, please provide more raw data in your table (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

      How to provide sample data in the Power BI Forum

       

       

      And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

      How to upload PBI in Community

      Best Regards