cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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     

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IX
Super User IX

Re: Week to week comparison - wrong total sum

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
Highlighted
Super User IX
Super User IX

Re: Week to week comparison - wrong total sum

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Helper I
Helper I

Re: Week to week comparison - wrong total sum

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.

 

Highlighted
Super User IX
Super User IX

Re: Week to week comparison - wrong total sum

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.

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors