Week to week comparison - wrong total sum

[Smokey24]

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     

Week 2 20       10     -10
Week 3 30       20    - 10

Week 4            30          

Total    60        60       0

Now I only want to calculte if both act. and prev. are existing:

             Act.  Prev.   Diff.

Week 1 10     

Week 2 20       10     -50%
Week 3 30       20    - 33,33%

Total     50       30     -40

To calculate the act. and prev. value I am using variables:

VAR act = CALCULATE(SUM('Table'[Column]))
VAR prev = CALCULATE(SUM('Table'[Column]); DATEADD ( 'MasterCalendar'[Date]; -7; DAY ))

VAR Diff = If(act <>; DIVIDE(act;prev))

This works for rows but the total for prev. and diff. are wrong. 

I saw an article where you had to use HASONFILTER and SUMX but I'm not sure how to this with Measures instead of Columns.

Could you help me out?

[Greg_Deckler · Accepted solution]

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

[Smokey24]

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.

[Greg_Deckler]

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.