cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HuntBA2 Frequent Visitor
Frequent Visitor

Combinig 2 charts

Morning folks,

 

I have 2 charts that use the same formating and when run seperately work fine, but when I try to combine the data, which ever set I add second sums the full year rather than the month. So I have a chart for months actual revenue, but when I try to add the planned numbers (for comparisson) if gives me the yearly data rather than months, and I can't seem to fix it. vice versa, if I run the plan numbers first it looks fine, and then whan I add the actual data it sums the full year. This data is pulled from 2 different spreadsheets, but as mentioned the date and currency format is the same.

 

Can anyone help with this? Thanks in advance, Brett

 

BI Issues 2.jpg

 

BI Issues 3.jpg

 

 

BI Issues.jpg

 

9 REPLIES 9
JosefPrakljacic Senior Member
Senior Member

Re: Combinig 2 charts

Hello HuntBA2,

 

if I have understood you correctly, then I assume that these two tables have no relation to each other.

 

I would recommend that you create a date table. Take a look at  --> Guy in a cube - Tutorial

 

You then relate your workshop tables to this one (in the Relationship section)

 

If you don't want to use a date table in between (which you should) you may add a calculated column in the workshop table and then join it with the other one.

 

Maybe you could also share your PBI file so that I can help you further out.

 

 

Community Support Team
Community Support Team

Re: Combinig 2 charts

Hi @HuntBA2

I can reproduce your problem, then i slove the problem with the following workaround.

 

1. firstly, don't keep any relationship between "workshop" and "workshop plan" tables.

create calculated columns in the "workshop" table

month1 = CONCATENATE(CONCATENATE(YEAR([invoice date]),"-"),MONTH([invoice date]))

month = CALCULATE(MIN([invoice date]),ALLEXCEPT(Sheet3,Sheet3[month1]))

invoice value per month = CALCULATE(SUM(Sheet3[invoice value]),ALLEXCEPT(Sheet3,Sheet3[month]))

 

2.create a new table with the "workshop" table

new table = SUMMARIZE(ALL(Sheet3),[month],Sheet3[invoice value per month])

3.edit relationship between "new table" and "workshop plan" table

then drag "date" and "plan" from "workshop plan" table, "invoice value per month" from "new table" onto the visual on the report.

5.png

 

 

Best Regards

Maggie

HuntBA2 Frequent Visitor
Frequent Visitor

Re: Combinig 2 charts

Hi Maggie,

 

Many thanks for the help on this, i'm getting an error saying Cannot find table "Sheet3"

 

Is there an easy fix on this?

Community Support Team
Community Support Team

Re: Combinig 2 charts

Hi @HuntBA2

Where do you use "Table3", could you show me a screenshot?

Below is my pbix

 

Best Regards

Maggie

HuntBA2 Frequent Visitor
Frequent Visitor

Re: Combinig 2 charts

Ok sorted that issue, i needed to change table3 to Workshop!

 

So I'm now still having the same issues, what relationship did you create between the 2? I have tried linking dates etc and i'm still getting the same error

Community Support Team
Community Support Team

Re: Combinig 2 charts

Hi @HuntBA2

Please pay attention to my first reply.

firstly, don't keep any relationship between "workshop" and "workshop plan" tables.

 

Best Regards

Maggie

HuntBA2 Frequent Visitor
Frequent Visitor

Re: Combinig 2 charts

OK - I'm a bit confused, if I remove any relationships I still have the same issues, is there another elationship your referancing in your 3rd bullit?

 

3.edit relationship between "new table" and "workshop plan" table

then drag "date" and "plan" from "workshop plan" table, "invoice value per month" from "new table" onto the visual on the report.

 

To clarify, the relationship I set up was between the plana nd new table.

 

Thanks

Community Support Team
Community Support Team

Re: Combinig 2 charts

Hi @HuntBA2

Sorry for my bad statement, let me edit it again

1. firstly, don't keep any relationship between "workshop" and "workshop plan" tables.

create calculated columns in the "workshop" table

month1 = CONCATENATE(CONCATENATE(YEAR([invoice date]),"-"),MONTH([invoice date]))

month = CALCULATE(MIN([invoice date]),ALLEXCEPT(workshop,workshop[month1]))

invoice value per month = CALCULATE(SUM(workshop[invoice value]),ALLEXCEPT(workshop,workshop[month]))

2.create a new table with the "workshop" table

new table = SUMMARIZE(ALL(workshop),[month],workshop[invoice value per month])

3.edit relationship between "new table" and "workshop plan" table

 

2.png

 

then drag "date" and "plan" from "workshop plan" table, "invoice value per month" from "new table" onto the visual on the report.

3.png

 

Best Regards

Maggie

 

HuntBA2 Frequent Visitor
Frequent Visitor

Re: Combinig 2 charts

Hi Maggie,

 

OK - so when trying to do the 1.1 it looks like you are suing i get this error

 

BI.jpg

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors