How do I show a cumulative line chart with individual contribution lines and Total line.

[acroft1162]

I have data that looks like this:

I want to show a pivotchart that has Cumulative Net Profit lines (i.e. running total) for each currency pair and a "Total" (all currencies) line. They must all show together, no filters. Problems:
(1) In my mind this is best achieved using some sort of dynamic table with similar columns - except the "Item" column will just show "Total" or something similar. This dynamic table would then be unioned with the base table above, then I have a single column giving me currency pairs and total. I do not know how to achieve this is in DAX though (I am more of a SQL guy!)
(2) The "Item" column will have an unknown (at time of loading) number of different currency pairs in it, so the solution must be dynamic as far as identification of "Item" attribute values is concerned.
(3) I am working in Excel but using the Data Model, I don't think this makes any difference. I could switch to PBI but that would cause a headache for some users. 

[Anonymous]

Hi @acroft1162 ,

The above two formulas are of course used in Power BI Desktop.

Do you want to show a cumulative line chart with individual contribution lines and Total line in Excel? Do you want this to be implemented in Excel instead of Power BI? If so, please post to the correct forum.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[acroft1162]

Where would I run the statement:

table2=summarize('table','table'[date],'table'[Currency Pair],'table'[Net Profit])

The solution is running in Excel/Power Pivot, not PowerBI. I cant revert to PBI for this particular task. 

[Anonymous]

Hi @acroft1162 ,

Please have a try.

Create a table with the following columns: Date, Currency Pair, Net Profit.

table2=summarize('table','table'[date],'table'[Currency Pair],'table'[Net Profit])

Then create a column.

column=CALCULATE(SUM(Table2[Net Profit]), FILTER(ALL(Table2), Table2[Currency Pair] = EARLIER(Table2Currency Pair]) && Table2[Date] <= EARLIER(Table2[Date])))

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.