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
Microsoft Employee
Microsoft 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.
dkay84_PowerBI
Microsoft Employee
Microsoft Employee

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.

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)