cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bullius Member
Member

Line chart with subtotals and total

Hello,

 

I am trying to create a line chart with three lines: Line A, Line B and the Total of Line A and B. At the moment, I can only do Line A and B OR the Total

 

Is there a way of having all three on the same chart?

 

I have tried creating a measure to return the total so that I can add it to the line chart, but it doesn't let me add it:

 

Total = CALCULATE(SUM(Table[Amount]), Table[Category]="Line A")+CALCULATE(SUM(Table[Amount]), Table[Category]="Line B")

 

Any thoughts?

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-qiuyu-msft
Moderator

Re: Line chart with subtotals and total

Hi @bullius,

 

In your scenario, I would suggest you create three measures: values for LineA, LineB and Total. Then place those three measures in chart Values property, without dragging any value to Legend property. Please refer to screenshots below:

 

Aliqui = CALCULATE(SUM(SalesFact[Units]),Manufacturer[Manufacturer]="Aliqui")

 

Natura = CALCULATE(SalesFact[Total Units],Manufacturer[Manufacturer]="Natura")

 

Total = CALCULATE(SUM(SalesFact[Units]),Manufacturer[Manufacturer]="Aliqui") + CALCULATE(SUM(SalesFact[Units]),Manufacturer[Manufacturer]="Natura")

 

z2.PNG

 

If you have any question, please feel free to ask.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
achinm45 Member
Member

Re: Line chart with subtotals and total

Hi @bullius

What is the message it gives ? Please do let us know.

 

 

bullius Member
Member

Re: Line chart with subtotals and total

It doesn't give me a message. It doesn't let me drag the measure on to the Legend field so that I have the Total as well as the two categories.

 

When I try to add the measure to the value field, it allows me to replace "Amount", but not have both. This results in Line A and B both showing the total of Line A and B (according to the measure).

bullius Member
Member

Re: Line chart with subtotals and total

I should have been clearer - it lets me create the measure, but does not let me add it to the line chart in a way that resuts in the three desired lines.

Moderator v-qiuyu-msft
Moderator

Re: Line chart with subtotals and total

Hi @bullius,

 

In your scenario, I would suggest you create three measures: values for LineA, LineB and Total. Then place those three measures in chart Values property, without dragging any value to Legend property. Please refer to screenshots below:

 

Aliqui = CALCULATE(SUM(SalesFact[Units]),Manufacturer[Manufacturer]="Aliqui")

 

Natura = CALCULATE(SalesFact[Total Units],Manufacturer[Manufacturer]="Natura")

 

Total = CALCULATE(SUM(SalesFact[Units]),Manufacturer[Manufacturer]="Aliqui") + CALCULATE(SUM(SalesFact[Units]),Manufacturer[Manufacturer]="Natura")

 

z2.PNG

 

If you have any question, please feel free to ask.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

bullius Member
Member

Re: Line chart with subtotals and total

That solution works. Thank you!

Highlighted
wildmight2017 Regular Visitor
Regular Visitor

Re: Line chart with subtotals and total

Sorry to re-animate old topic - but I'm having the same issue and proposed solution does not work (as it's not practical once you hit more than 5-6 categories). Is there an easier way to bring Total line to the line chart? Thank you! 

shishir999 Regular Visitor
Regular Visitor

Re: Line chart with subtotals and total

Hi, In Line chart, I am able to calculate the Total. But in my scenario, there are 3 Status (Success, Failure, In-Progress) in filter. S=5, F=3, IP = 2 ; Total=10 Once, I filter the status (Success), the user want to see the status =5 for the "SUCCESS" and the Total of ALL Status=10. But, it is only calculating status=5. *********** CALCULATE(COUNTROWS(ALL_Record), Filter((ALL_Record), ALL_Record[Build_Date] <= MAX(Build_Type[Date]) )) ************* Could someone, please help in this. Shishir
cmihale Frequent Visitor
Frequent Visitor

Re: Line chart with subtotals and total

The solution needs to be dynamic - new values may be added to the field in question in the future.  Hard-coding a new value would mean manually maintaining it in the future. Not a very scalable option.

 

Confused as to why this was not built into Power BI, as this is basic functionality in excel.  The "total" value is simply the result when you remove a field from the "group by" section.  

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 133 members 1,815 guests
Please welcome our newest community members: