Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Smokey24
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
Greg_Deckler
Super User
Super User

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.