Creating a Running Total by Date, Stacked Area Chart by Category

[Anonymous]

Hello - I'm trying to do something in Power BI that I can do fairly easily in excel and I was wondering if I am missing something.

I'm starting from a data table like this:

In Excel, i would then pivot the data as follows (with running totals):

And then create a pivot chart as follows:

I can't figure out how to do any of this in Power BI.

Any help would be MASSIVELY appreciated - thanks so much.

Adam

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Thank you for the reply; however, the graph that is produced here is does not appear to be the same as the graph that I produced with the Pivot Chart.  The amounts do not appear to be stacked (i.e. the top amount on the y axis is not $50,450.  Is there any way to configure it the same way that I have it?

[Ashish_Mathur · Accepted solution]

Hi,

You may download my revised PBI file from here.

Hope this helps.

[aejohnson04]

Hi Ashish, I found your solution and it is exactly what I'm needing. Although the measure to calucate the amount for all states is not working properly for me. For some reason it is adding a column with a blank header that sums the entire data table. I believe this is what is causing the mis calculation in my RT. Do you have any insight as to why this would be happening? Please see screenshot below

[Anonymous]

Ashish -

Thank you so very much!!!  

You are awesome.  We've been trying to figure this out for a long time.

Best regards,

Adam

[Ashish_Mathur]

You are most welcome.  Thank you for your kind words.

[VasTg]

@Anonymous 

Adam, Could you paste the sample data instead of a picture of sample data?

[Anonymous]

See below - thanks!

Date	Type	Amount	State
1/1/2019	Car	1000	PA
1/1/2019	Truck	2000	WA
1/3/2019	Car	1500	PA
1/5/2019	Truck	3000	PA
1/7/2019	Minivan	2000	CA
1/10/2019	Car	4500	WA
1/10/2019	Car	1200	PA
1/10/2019	Car	1500	CA
1/12/2019	Truck	1000	CA
1/15/2019	Car	2000	WA
1/15/2019	Minivan	2500	PA
1/20/2019	Truck	1500	WA
1/20/2019	Truck	1700	CA
1/21/2019	Car	2000	PA
1/23/2019	Truck	4000	PA
1/23/2019	Car	1200	PA
1/25/2019	Minivan	2500	WA
1/25/2019	Car	1000	PA
1/29/2019	Truck	1200	CA
1/29/2019	Minivan	1900	CA
1/30/2019	Minivan	4000	WA
1/30/2019	Truck	2500	PA
1/30/2019	Car	2750	WA
1/31/2019	Truck	2000	CA

[VasTg]

@Anonymous 

UPDATE:

I completely missed the running total. Use the DAX below to create the running total and use it in the graph instead of "Amount"

Replace the table and column names. Table - Data; Column - Date,Amount

Running Total =
VAR DATE1 =
    MAX ( Data[Date] )
RETURN
    CALCULATE (
        SUM ( Data[Amount] ),
        FILTER ( ALL ( Data[Date] ), Data[Date] <= DATE1 )
    )

Its easy in Power BI too. 

I used your sample data and created this graph. 

Just connect to your data with excel as data source and select the sheet you want to load. Choose edit,Query editor window opens, click on the column header( Left side of each column -  where you see ABC,123) to select the correct datatypes in Query Editor.Close & Apply.

In the report window choose the graph i pointed out, drag and drop the attributes or measures in the highlighted area. 

To change the column format, open data window, choose the column and you could the format in the top ribbon.

If this helps, mark it as a solution,

Kudos are nice too.