Forum Discussion

Stuquan's avatar
Stuquan
Frequent Visitor
4 years ago
Solved

Cumulative value per week (average)

Hi there,

 

I'm trying to figure out how to get a cumulative value working for a measure I've made called 'Avg £. Per week'. I have a cumulative figure working for one of my columns already but I don't believe you can reference another measure inside another?

 

Here is a sample of my data:

 

YearWeek NumberSum_Per_WeekCumulative_Per_WeekAvg. £ Per Week
20221£213,115£213,155£26,639
20222£22,000£235,155£11,000
20223£1,000£236,115£500
20224£821,417£1,057,532£31,592

 

Basically, I'm trying to do the same I've done for column 4 to reference column 5 and total up so for example the 1st cumulative value would be £37,639 and so on.

 

This is the code I use for the first cumulative value column:

 

Cumulative_Value =
CALCULATE (
SUM ('Enrichment Submissions'[How much will this change benefit the business financially? (Incremental Revenue, Cost saving etc.)] ),
FILTER ( ALL (Virtual_Table_1), Virtual_Table_1[Week_Number]<= MIN (Virtual_Table_1[Week_Number])),(Virtual_Table_1[Year]<= MIN (Virtual_Table_1[Year])
))
 

And this is my 'Avg. £ Per Week' code:

 

Avg. £ Per Week =
CALCULATE(AVERAGE('Enrichment Submissions'[How much will this change benefit the business financially? (Incremental Revenue, Cost saving etc.)])/DISTINCTCOUNT('Enrichment Submissions'[Week_Number]),FILTER('Enrichment Submissions','Enrichment Submissions'[Year]))
 

I'm thinking I need to somehow merge the filter from my cumulative_value column into the Avg. £ per week one but I can't seem to get it to work, does anyone have any tips?

 

Thanks!

Stew

Need Help
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, Stuquan 

     

    If your average is a calculated column, you can try the following method directly.

    Column:

    Cumulative Avg =
CALCULATE (
    SUM ( 'Table'[Avg. £ Per Week] ),
    FILTER ( 'Table', [Week Number] <= EARLIER ( 'Table'[Week Number] ) )
)

    Did this result meet your expectations?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Stuquan , Create a separate year week table with Year *100 + week is the , join with same key in the fact 

     

    Create a rank on year week

    depending on date or week

    new columns
    Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
    Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
    Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
    OR
    Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

     

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

     

    Avg Cumm Week = CALCULATE(AverageX(values('Date'[Week Rank]) , calculate( sum('Table'[Qty])) , FILTER(ALL('Date'),'Date'[Week Rank]<=max('Date'[Week Rank])))

     

     

    This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
    Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
    Last year Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))

     

    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-Last-Week/ba-p/1051123
    https://www.youtube.com/watch?v=pnAesWxYgJ8

  • v-zhangti's avatar
    v-zhangti
    Community Support
    4 years ago

    Hi, Stuquan 

     

    If your average is a calculated column, you can try the following method directly.

    Column:

    Cumulative Avg =
CALCULATE (
    SUM ( 'Table'[Avg. £ Per Week] ),
    FILTER ( 'Table', [Week Number] <= EARLIER ( 'Table'[Week Number] ) )
)

    Did this result meet your expectations?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.