Forum Discussion

Smokey24's avatar
Smokey24
Icon for Helper I rankHelper I
6 years ago
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     

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?

 

Need Help

3 Replies

    • Smokey24's avatar
      Smokey24
      Icon for Helper I rankHelper I
      6 years ago

      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's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        6 years ago
        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.