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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Nickodemus
Helper III
Helper III

Stop Summarising Data in visualisation

 Hi All,

 

New user here - this is my first post on here...

 

I'm really struggling to show price data in a visualisation.

I have a range of products sold in a number of stores, each with a specific price, however, this price can change every week for any product in any store.

 

My data table has a row for every product, year, week, with a price specified in each row.

Example:

Store_ID    Product_ID    YEAR    WEEK    PRICE

Store_ID_1    Product_1    2017    1    150.00

Store_ID_1    Product_1    2017    2    150.00

Store_ID_1    Product_1    2017    3    152.00

Store_ID_1    Product_2    2017    1    90.00

Store_ID_1    Product_2    2017    2    91.00

Store_ID_1    Product_2    2017    3    91.00

Store_ID_2    Product_1    2017    1    135.00

Store_ID_2    Product_1    2017    2    137.00

Store_ID_2    Product_1    2017    3    144.00

Store_ID_2    Product_2    2017    1    89.00

Store_ID_2    Product_2    2017    2    90.00

Store_ID_2    Product_2    2017    3    90.00

 ...and so on....

 

I'm trying to create a visualisation that shows, on one line chart, each products' price varying over the weeks for each store, but I can't get PowerBI to show the actual price for each week.... it just always wants to show an average.

 

I have set the price column to not summarise in the data modelling, but this hasn't helped.

 

I have been able to create a matrix of the data which looks perfect, but I need this as a visual so that i can show trends.... is this possible please?

 

Many thanks.

3 REPLIES 3
v-yulgu-msft
Employee
Employee

Hi @Nickodemus,

 

We are able to use a Matrix visual to show the trend of each products' price varying over the weeks for each storeID, because we can add multiple row groups or column groups in a matrix, as is shown from below image.
1.PNG

 

However, if you want to convert this matrix to a line chart, ideally, there should display four lines to show the trend on one chart. It is not available to do that now, because we are not allowed to add both [StoreID] and [ProductID] into legend. So, I'm afraid, your requirement cannot be achieved now.

 

By the way, in a chart, values will be aggregated by default, but if we desgin the chart correctly, the aggregated result is the same as its actual value.

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Without seeing your data model or PBIX file it is hard to answer, but my guess is that if you are using weeks as the axis, there are multiple prices per week for a given product, and thus you will get some aggregation (i.e. average).  You need to have exactly one data point per product per store for every X axis increment to avoid aggregating.  

 

Try creating a new column:

 

[Year]*100+[Week] 

 

This will give you an index, i.e. 201701, 201702 etc.  Use this as your axis and see if it works better.

 

Feel free to share the PBIX if you want a more precise answer (or give us your data, anonomyzed is okay).

@dkay84_PowerBI - thanks for the reply. I've tried that, i think i'm getting somewhere.

Screen Shot 2017-02-24 at 09.13.15.png

But I have had to adjust my expectations, and have applied a filter for each store to see individual product level data. At the aggregated level it shows average product price per store, which is probably still quite interesting.The next problem I  have is that, because the price changes are so small and relatively infrequent, the chart looks pretty flat. Is there a good way to amplify the appearance of the line, to exaggerate the changes?

 

@v-yulgu-msft- thanks for you reply too. That matrix looks exactly like the one I've managed to create... I think I'm finally getting there.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.