Issues with SumX Function -- Table Totals Incorrect

[powerbinovice01]

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! 

  @amitchandak 

@parry2k 

[powerbinovice01]

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! 

[v-yiruan-msft]

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

[Greg_Deckler]

@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]

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]

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

[Greg_Deckler]

@foodd Well, I do love talking Power Bi measure totals after all... No really, the first thing I check for when I visit the forums is if there are any measure total questions I can answer... 🙂

And thanks for the kind words!

[Greg_Deckler]

@powerbinovice01 With what I am picturing in my head, it should be something along the lines of this:

Total Weekly Difference =
  VAR __Table = 
    SUMMARIZE(
      'Weekly Sales',
      [Weekly Report Date],
      [Vendor Name],
      "Diff", [Diff Between Weekly TW and LW]
    )
  VAR __Result = IF( HASONEVALUE('Weekly Sales'[Weekly Report Date]), [Diff Between Weekly TW and LW], SUMX( __Table, [Diff] )
RETURN
  __Result

You would use this measure in your visual instead of the "Diff Between TW and LW" measure. For rows, it simply returns the Diff Between TW and LW measure value. But for the total row, it will return the summed value across the virtual table which should be summarized exactly the same as your visual summarizes the data.

[Greg_Deckler]

@powerbinovice01 Any chance you can paste some sample data from your Weekly Sales table? Having trouble visualizing it in my head based on the formulas.