Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
powerbinovice01
Frequent Visitor

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! 
powerbinovice01_0-1695322671559.png

 

8 REPLIES 8
powerbinovice01
Frequent Visitor

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

powerbinovice01_0-1695665364433.png
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')

powerbinovice01_1-1695665590113.png

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. 

 

powerbinovice01_2-1695665793248.png

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! 

 

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Greg_Deckler
Super User
Super User

@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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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! 

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

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.