Forum Discussion

Smokey24's avatar
Smokey24
Helper 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 ...
Need Help
Smokey24's avatar
Smokey24
Helper 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
    Community 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.