Forum Discussion

HuntBA2's avatar
HuntBA2
Regular Visitor
7 years ago

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

 

 

 

 

 

Need Help

9 Replies

  • JosefPrakljacic's avatar
    JosefPrakljacic
    Icon for Solution Sage rankSolution Sage
    7 years ago

    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.

     

     

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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.

     

     

    Best Regards

    Maggie

    • HuntBA2's avatar
      HuntBA2
      Regular Visitor
      7 years ago

      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?