Forum Discussion

CP000's avatar
CP000
New Member
6 years ago
Solved

Running Total with multiple items

Dear Community,    I think I have a more complex Running Total challenge. I have my information arranged by Date, Type and Value. I need to create a monthly running total by date and type.   In ...
Need Help
  • TomMartens's avatar
    TomMartens
    6 years ago

    Hey CP000 ,

     

    i'm not sure what you mean by running total by date and type. If you select tpye either by slicer, axis label, or row/column header this DAX creates a cumulative value based on type, even if type is not mentioned inside the formula by itself:

     

    running total test = 
CALCULATE(
    SUM('Fact Sale'[Quantity])
    , FILTER(
        ALL('Fact Sale'[Invoice Date Key])
        , 'Fact Sale'[Invoice Date Key] <= MAX('Fact Sale'[Invoice Date Key])
    )
)

     

    Here is a little screen shot:

    If this does not provide what you are looking for, please give a more detailed explanation what makes the type column special.

     

    Regards,

    Tom

     

CP000's avatar
CP000
New Member
6 years ago

Thank you Tom, can you show me your table in your query editor? 

 

What I mean with Date and Type is:

1/1/2020 1 Budget Target 12,500

2/1/2020 1 Budget Target 25,000

3/1/2020 1 Budget Target 32.500

... and so on.

You see, my table gets complex beacuse I have differente types of data with different values: 1 Budget Target; 2 Forecast; 3 Expected; 4 Actual

 

TomMartens's avatar
TomMartens
Super User
6 years ago

Hey CP000 ,

 

if adjust my measure to your table name and column name, it's sufficient to use the date column as axis, the measure as value and the type column as legend, this will create 4 lines.

 

If this is not what you are looking for, create a pbix that contains sample data, but still reflects your data model. Create a sketch how your visual should look like import the sketch to the pbix, upload the pbix to onedrive or dropbox and share the link.

 

Regards,

Tom