Create cumulative Line chart with total line and dynamic with slicer value

[SatyamPrajapati]

I have dataset with Date, Site name, Product Name, Sales Amount. I want to create cumulative line chart with sales by different products (Different lines for every products), Also I want to create Total line of that products in one line chart. It should be dynamic based on selected value in slicers (Year) & (Site name). 

I want to create chart with X-Axis as Month-Year (mmm-yyyy), Y-Axis as Running total of Sales Amount, legends as different Products and Total of different products.

1> Method : Running Total
I tried to create it by using quick measure -> running total. it works perfectly dynamic with slicers but with this method I didn't get separate Total line. 

2> Method : Createing Parameter

I tried to create line chart using Parameter by all products and total. Chart perfectly created with different peoduct line and Total line, But it didn't work with silcer. 

SO, please provide guidence regarding how can I create cumulative line chart with total line which is dynamic with slicer values.

[danextian · Accepted solution]

What do you mean it doesnt change? The measure still references the fact table that other dimension tables have a relationship to. You can see in the image below that the viz still responds to the slicer selection as the underlying measure references a table that is related to geo.

[danextian]

Hi @SatyamPrajapati 

For this, you will need a disconnected table that contains the products and another row for the total and then you create a measure to return the value per category and the total value.

Total Revenue2 = 
IF (
    SELECTEDVALUE ( Category_with_total[Category] ) = "Total",
    [Total Revenue],
    CALCULATE (
        [Total Revenue],
        KEEPFILTERS (
            TREATAS ( VALUES ( Category_with_total[Category] ), Category[Category] )
        )
    )
)

 In the screenshot below, you can see that there is a separate line for total.

You can then apply the cumulative calculation to this measure. Please see attached pbix for the details.

[SatyamPrajapati]

Hi @danextian,

I already tried this but it didn't work dynamically. As it disconnected table chart doesn't change based on slicer's seleted value (Site Name).

[danextian · Accepted solution]

What do you mean it doesnt change? The measure still references the fact table that other dimension tables have a relationship to. You can see in the image below that the viz still responds to the slicer selection as the underlying measure references a table that is related to geo.

[SatyamPrajapati · Accepted solution]

Thank You @danextian