DAX Power BI calculate the product of different parameters in two columns and display line graph with

Question

I've got a problem that I can't seem to solve in a simple way, only an overly complex way. So basically, I've got two columns in a table. In one columns is a waste parameter/category (Concrete, plastic, wood, etc.) and in the other is a corresponding CO2 value for each category (1.356, 1.009, 3.5, etc.). The waste parameter is related to a specific "delivery" of waste with a corresponding date, both of which are columns in the table (but I only think I need to use the delivery date column).

I need to create a graph that displays the cumulative CO2 for each waste category over time. Basically, I'm struggling to find a way to create "Cumulative CO2" consumption that I can then use to display it for each category over time.

Do you have any suggestions how I might do this in the most effective way? How might I write that DAX query? Or do you know of any resource that might help me solve this problem?

Thanks in advance to any help available!

The expected result: A column with the cumulative CO2 by Type

Answer 1

Still guessing what you want. Sample data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrISSwuyUwGsgyNTJVidaKVjFBEjY2NwaLGQHZ4fn4KkkITINs3tSQxB6TMwAAsZoqkDCpkBmQH52eCVBmaQoTMkXQagZTFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Row = _t, Category = _t, Weight = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Row", type text}, {"Category", type text}, {"Weight", Int64.Type}})
in
    #"Changed Type"

And what you want is probably running total by category. If you put Category and Row (or date, something on which you want running total) in the table visual than this DAX code will work.

Running Total = 
CALCULATE (
    SUM ( 'Table'[Weight] ),
    FILTER (
        ALL ( 'Table'[Row] ),
        'Table'[Row] <= MAX ( 'Table'[Row] )
    )
)