Forum Discussion
Solved
Week to week comparison - wrong total sum
Hi, in a table I have a column showing me the sales per week and the column next to it shows me the sales from previous week: Looks like this: Act. Prev. Diff. Week 1 10 ...
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
Hi Greg,
in your "Quick Measure, Measure Totals, The Final Word". could you please explain me how
VAR __table = SUMMARIZE('Table1';[Name];"__value";[m_Single])
RETURN
IF(HASONEVALUE(Table1[Name]);[m_Single];SUMX(__table;[__value]))
works? what does "__value" actually do?
I tried a bit and when I delete it, I just get an error.
Thanks.
Sure, the first line with the variable __table, this uses SUMMARIZE to essentially group 'Table1' by the Name column and then to this grouping, it adds a column called __value which takes on the value of whatever your measure is for a single row. So, effectively, you are recreating the table or matrix visualization as displayed in memory and storing this in the table variable, __table.
In the return statement then, you check if Name has one value, if so you are in a row in the table and you return the measure for a single row (m_Single), otherwise you use SUMX over __table summing the __value column.
In the return statement then, you check if Name has one value, if so you are in a row in the table and you return the measure for a single row (m_Single), otherwise you use SUMX over __table summing the __value column.