One product on a few lines in table view

[gbarr12345]

Hi there,

I have a power BI report using transactional data I added in via excel.

The issue I'm seeing is that one product has numerous lines when I only want it in one line.

Would you have any idea on how to get all these lines into one or is this possible?

[WanderingBI · Accepted solution]

Hi,

you propably have a data table like this:

"PRODUCT DESCRIPTION" "MARGIN"

A 5

A 3

A 2

It looks like you are using the table visual. The table visual in standard settings will show all your data for the columns that you add to it as it will simply show the values from your input data.

It really depends on what your goal is. One way to get this one product show in one line is to aggregate the values from your input data in some way. One example would be to calculate the average of all the values, like so:

[Ritaf1983]

Hi @gbarr12345 
The Y-axis in a line chart functions as a value axis, inherently performing aggregation, much like a pivot table in Excel.

So if you need to show the values of every row separately, and there is no unique id for every row inside product you need to create an index like :

Then you can use this index as a X axis values :

The sum function is just for dragging the margin, as the X is a unique value it shows every point exactly as in the table

To add the index you can use a method from the linked  article :

https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query

Pbix is also attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[gbarr12345]

Hi Rita,

Thanks for the quick response.

That is helpful, however what I'm trying to do is to make them only 1 entry.

The Ara single EST is one product but there's various transactions for it. 

I'm looking to only have it as one line in a table view visualisation you see.

Is the fact that there's various transaction making it difficult to ahvieve this?

[Ritaf1983 · Accepted solution]

@gbarr12345 
I think at this stage the main challenge is to understand what the desired outcome is 🙂

So as usual, I recommend showing the desired result.

[gbarr12345]

Hi Rita,

Actually figured it out based on Ibanez's comment below.

I set it to Average and it brought the average figure for the products so only brought in one figure now.

Thanks for your help all the same, really appreciate it 🙂 

[WanderingBI · Accepted solution]

Hi,

you propably have a data table like this:

"PRODUCT DESCRIPTION" "MARGIN"

A 5

A 3

A 2

It looks like you are using the table visual. The table visual in standard settings will show all your data for the columns that you add to it as it will simply show the values from your input data.

It really depends on what your goal is. One way to get this one product show in one line is to aggregate the values from your input data in some way. One example would be to calculate the average of all the values, like so:

[gbarr12345]

You're a STAR!!!

That worked thank you so much 🙂