Forum Discussion
Issues with SumX Function -- Table Totals Incorrect
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
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!
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 __ResultYou 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.
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.
