Compound Values

[Tango2310]

Hi,

New to Power BI.

I have a table of date an invoice values and am trying to work out how to make the line compound in other words build on the totals to the left.

In other words

1st data point would be sum of the 01/08

2nd data point would be the  sum of the 02/08 + the sum of the 01/08

3rd data point would be sum of the 03/08 + the sum of the 02/08 + sum 01/08

and so on.

Any help as to how to achieve this would be greatly appreciated.

Thanks

Todd

[Ashish_Mathur]

Hi,

Try this approach

1. Create a Calendar table with calculated column formulas for Year, Month name and Month number.  Sort the Month name by the Month number.
2. Create a relationship from the Date column of the Data Table to the Date column of the Calendar table
3. To the X-axis, drag Date from the Date column of the Calendar Table
4. Write these measures

Total = sum(Data[invoice value])

RT = calculate([Total],datesbetween(calendar[date],minx(all(calendar),calendar[date]),max(calendar[date])))

Hope this helps.

[Tango2310]

Thank you everyone

[Anonymous]

Hi @Tango2310 ,

Do you want to find the composite value of the month?

If so, I did a test for your reference.

In my scenario:

My Model View:

Table:

Table 2 =SUMMARIZE('Table','Table'[Date],"sum",SUMX('Table',[Invoice Value]))

My Report View:
my =var _table=SUMMARIZE(ALL('Table'),'Table'[Date],"sum",SUMX('Table',[Invoice Value]))
return
SUMX(FILTER(_table,[Date]<=MAX('Table'[Date])),[sum])

Best Regards,

Sunshine Gu

[Tango2310]

Thank you @Anonymous what would the query be if i was just creating a measure?

[SachinNandanwar]

Just incase if you want to display running total across

Running Total =
VAR _Total =
    CALCULATE (
        SUM ( Inv[Invoice Value] ),
        OFFSET (
            -1,
            (
                ALLEXCEPT (
                    Inv,
                    Inv[Date].[Date]
                )
            ),
            ORDERBY ( Inv[Date], DESC ),
            PARTITIONBY ( Inv[Date] )
        )
    )
RETURN
    _Total + SUM ( Inv[Invoice Value] )

each row

[AnkitaaMishra · Accepted solution]

Hi @Tango2310 ,
please refer below video to create running total measure using DAX : 
https://www.youtube.com/watch?v=NBGGHdKxupk&t=347s

use that measure to plot your graph.
let me know if this works for you or share additional details for your requirement.

Thanks,

Ankita

[TrevLc]

You can use visual level calculations to create a running sum formula, or you can create a measure like this:

running sum =

CALCULATE(sum('Table'[Amount]),

FILTER (

ALL ( 'Table' ),

'Table'[Date] <= MAX ( 'Table'[Date] )

))

[Tango2310]

Thanks @TrevLc, Im not getting error messager with your approach but the numbers are way incorrect