View My Drafts
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.
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.
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.