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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
DJSwezey
Helper I
Helper I

Sort Order ignores measure??

I have a simple 'Sales' table below. I created a running total measure to compile the [Goal] amounts each week.

When I have the [Week of] sorted by default to [Week of] my measure works fine and the Goals are combined each week over week. When I sort [Week of] by [Order id] then the measure only sums each week individually in the measure. Any idea why this would be happening? 

DJSwezey_1-1683048319092.png

 

Goal Running Total = CALCULATE(
    sum(Sales[Goal]),
    FILTER(
        all(Sales[Week Of]),
        Sales[Week Of] <= MAX(Sales[Week Of])
    )
)

 

DJSwezey_2-1683048480511.png

 

(Sorted by [Week of])

DJSwezey_4-1683048885828.png

 

(Sorted by [Order id])

DJSwezey_3-1683048569374.png

 

 

 

1 REPLY 1
amitchandak
Super User
Super User

@DJSwezey , This will work well when you have separate week table with rank on year week column.

 

Have a separate date/week table joined back with your table

Have these new columns in Date Table, Week Rank is Important in Date/Week Table

Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format

 

 

cumm = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]<=max('Date'[Week Rank])))

 

I think simple formula with date will also work

 

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

 

or

 

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))

 

consider new window function with date or week

 

Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALL('date'[date]),ORDERBY('Date'[date],ASC)))

Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALLSELECTED('date'[date]),ORDERBY('Date'[date],ASC)))

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8
Time Intelligence, Part of learning Power BI https://youtu.be/cN8AO3_vmlY?t=27510

 

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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