Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
SatyamPrajapati
Regular Visitor

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

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.

2 ACCEPTED SOLUTIONS

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_0-1731752590517.gif

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

View solution in original post

4 REPLIES 4
danextian
Super User
Super User

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.

danextian_0-1731750728074.png

 

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










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

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).

 

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_0-1731752590517.gif

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Thank You @danextian 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!