Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Plotting different year onto a single chart in PowerBI

  • 1.  Plotting different year onto a single chart in PowerBI

    Posted 4 days ago
    Edited by LUMISA A 4 days ago

    Hi all,

    Very new to power BI and eager to learn and be involved in the community =)

    First i will show what i did previously using powerquerry and pivot chart in excel 2016 and then i show where i am stuck in powerBI

    Basically i have a simple table querry from SQL:

    Then after , using a pivottable i put the year and values in columns in pivottable field and the pivot chart shows what i want:

    * for the moment ignore the 2nd Y axis (bar chart) and also the moving average line 

     

    Using Get data in powerBI i querry the same table and i am able to get:

     

    I am able to get the a chart of the Average Price, Min Price, Max Price  vs Day OR Average price of 2017, 2018, 2019 vs Day but i like to have ALL 9 lines in the same chart ( as per excel)

    I have been trying this for the past few hours but i cant seem to figure it out weather its by column hierachy etc. I have transform the Date into seperate 3 columns to be year, Month, Date so i could avoid the Date Hierachy but it doesnt work as well.

     

    PS: Fecha is Date and Precio is Price. I do not need the Moving average as seen in my excel example for now but it would be good to have too.

     

    Regards



  • 2.  RE: Plotting different year onto a single chart in PowerBI

    Posted 4 days ago

    Hi @LUMISA A


    There is a lot going on in this question; I will do my best to answer.

    (1) If you know PowerPivot in excel, then you are well on your way to understanding Power BI. Same tabular engine in Power Pivot, Power BI, and Analysis Services. ​

    (2) The default setting in PowerBI is to create a 'hidden' date table when dates are loaded into your model which creates the automatic hierarchy. You can turn this feature off (as is best practice) by following the steps here: https://www.sqlbi.com/articles/automatic-time-intelligence-in-power-bi/. This way, when you put date on the X-axis, date is displayed
    (3) I am not sure if I understand the last question, If you created the measures in PowerPivot, do the same in PowerBI. Create the desired measures (e.g., Avg, Min, Max, Rolling Average, etc.) and then load them onto a single graph. 

    Hope this helps; reach out with any more questions. 

    Thanks!
    William



    ------------------------------
    William Rodriguez
    Business Analyst | MCSA: BI Reporting
    ------------------------------



  • 3.  RE: Plotting different year onto a single chart in PowerBI

    Posted 4 days ago
    Edited by LUMISA A 4 days ago
    Hi william,
    Thanks for your reply and the link tto turn off the feature is not only useful by itself but also the other articles there.
    1. and 2. are perfect

    3. In this situation, i do not use any measures. All the values are directly from our sql server without the need to do any further calculations. (See the table in first image, the min , average , etc have been pre calculated and presented as a value) The only thing  in excel 2016  i do is using powerquerry to create an addtional of 3 calculated columns to transform 'Date' to Date (Year), Date (Month), Date (Day)

    I try  to explain in steps:
    I have a table (datasource) of this:
    In excel pivot table(Not power pivot) field list this is  where i drop the columns and values:
    and pivot chart would produce this (similiar):

    Bascially with my table above (4 columns sourcedata of Date, Price Min, Price Average, Price Max) , i like to plot Price vs Month/Day with 9 series of 2017Min, 2017Avg, 2017Max, 2018Min, 2018Avg, 2018Max, 2019Min, 2019Avg, 2019Max.
    Prefereably as i have in pivot chart too , a filter for Month.

    I hope i explain well.
    Thanks for reading =)









  • 4.  RE: Plotting different year onto a single chart in PowerBI

    Posted 4 days ago
      |   view attached

    Hi @LUMISA A:


    Thanks for explaining! 

    Wow, interesting how things that come naturally in Excel are a little harder to configure in Power BI. 

    This is what I figured out: a line chart in PBI can only support one measure (column, measure, etc.) if you have a X-Axis and a legend, but can support multiple measures if you only have a X-Axis. So, the two options I have determined are:
    1) Use only the X-Axis and create desired measures for your out put. (i.e., OptionOne tab in PBIX file)
    Downside, needs to be constantly updated every year...
    2) Unpivot your data and create a new column where you combine the original description with the current year. Then graph the X-Axis, the category, and the value. (i.e., OptionTwo tab in the PBIX file)

    In my opinion, Option Two is best for longevity. 

    This is an interesting problem and i'm glad you brought it up! Hopefully there is an easier solution, but this is what I came up with. 

    Thanks!
    William



    ------------------------------
    William Rodriguez
    Business Analyst | MCSA: BI Reporting
    ------------------------------

    Attachment(s)

    pbix
    Lumisa_Help.pbix   54K 1 version