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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
HuntBA2
Regular 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
v-juanli-msft
Community Support
Community Support

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

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?

Hi @HuntBA2

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

Below is my pbix

 

Best Regards

Maggie

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

Hi @HuntBA2

Please pay attention to my first reply.

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

 

Best Regards

Maggie

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

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

 

Hi Maggie,

 

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

 

BI.jpg

JosefPrakljacic
Solution Sage
Solution Sage

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.

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.