Forum Discussion

Tango2310's avatar
Tango2310
Helper I
2 years ago
Solved

Compound Values

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

Need Help

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Tango2310
      Helper I
      2 years ago

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

  • TrevLc's avatar
    TrevLc
    Helper III
    2 years ago

    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's avatar
      Tango2310
      Helper I
      2 years ago

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

       

       

  • SachinNandanwar's avatar
    SachinNandanwar
    Impactful Individual
    2 years ago

    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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    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.