Forum Discussion
Running Total on Measure on a Measure
I have try to calculate a RT where I have Mesures that is already a Measure.
The [Test] Measure is 3 Measures and it shows the correct output on every YearWeek.
[Order_IO_Prod] is where I try to Accumulate the [Test] Value into a Running Total.
First value 42.11 is correct, then should it be 42.11+10.11=52.22 then 52.22 +-14.89=37.33
Instead it takes every row ex 10.11x2=20.22 next -14.89x3= -44.67 next 11.11x4= 44.44 aso
Hi DrillDownBI ,
I created some data:
Whether your [YearWeek] is in text format or not, you can create an Index to be used as a sorting sequence for cumulative calculations.
Here are the steps you can follow：
1. Create calculated column.
Rank = RANKX( 'Table','Table'[Date],,ASC)Index = MAXX( FILTER('Table','Table'[YearWeek]=EARLIER('Table'[YearWeek])),[Rank])2. Create calculated table.
Table 2 = SUMMARIZE( 'Table','Table'[YearWeek],'Table'[Index])3. Joining a relationship between two tables.
4. Show the expression order_IO_Prod in a separate measure.
5. Create measure.
True = var _today=TODAY() var _todayindex=MAXX(FILTER(ALL('Table 2'),'Table 2'[YearWeek]=FORMAT(_today,"YYYY-WW")),[Index]) return SUMX( FILTER(ALLSELECTED('Table 2'), 'Table 2'[Index]>_todayindex&&'Table 2'[Index]<=MAX('Table 2'[Index])),[order_IO_Prod])6. Result:
If it doesn't meet your desired outcome, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
2 Replies
DrillDownBI Pretty sure that you need to create a virtual table (VAR) that SUMMARIZE by YearWeek and includes your original measure as a column (either as part of the SUMMARIZE or ADDCOLUMNS). Then you can use ADDCOLUMNS to add the running total. You can then return your running total. Something like this:
Order_IO_Prod = VAR __MaxDate = MAX('Calendar'[YearWeek]) VAR __Table = SUMMARIZE(FILTER('Calendar', [YearWeek] <= __MaxDate), [YearWeek], "__Value", [Original Measure]) VAR __Table1 = ADDCOLUMNS( __Table, "__RT", VAR __YW = [YearWeek] VAR __Return = SUMX( FILTER( __Table, [YearWeek] <= __YW), [__Value] ) RETURN __Return ) VAR __Return = MAXX(FILTER( __Table1, [YearWeek] = __MaxDate), [__RT]) RETURN __ReturnHi DrillDownBI ,
I created some data:
Whether your [YearWeek] is in text format or not, you can create an Index to be used as a sorting sequence for cumulative calculations.
Here are the steps you can follow：
1. Create calculated column.
Rank = RANKX( 'Table','Table'[Date],,ASC)Index = MAXX( FILTER('Table','Table'[YearWeek]=EARLIER('Table'[YearWeek])),[Rank])2. Create calculated table.
Table 2 = SUMMARIZE( 'Table','Table'[YearWeek],'Table'[Index])3. Joining a relationship between two tables.
4. Show the expression order_IO_Prod in a separate measure.
5. Create measure.
True = var _today=TODAY() var _todayindex=MAXX(FILTER(ALL('Table 2'),'Table 2'[YearWeek]=FORMAT(_today,"YYYY-WW")),[Index]) return SUMX( FILTER(ALLSELECTED('Table 2'), 'Table 2'[Index]>_todayindex&&'Table 2'[Index]<=MAX('Table 2'[Index])),[order_IO_Prod])6. Result:
If it doesn't meet your desired outcome, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
