Forum Discussion
How to Display Rows Without Aggregation in Table Visual?
Hello ,
I am facing an issue with displaying data in a table visual. My data consists of multiple columns, and I want to show each row without summarizing the values. Here is an example of my data:
Column 1 Column 2 Column 3 Column 4 Column 5
| Value A1 | Value B1 | Value C1 | Value D1 | 10000 |
| Value A1 | Value B1 | Value C1 | Value D1 | 10001 |
| Value A1 | Value B1 | Value C1 | Value D1 | 10002 |
| Value A1 | Value B1 | Value C1 | Value D1 | 10003 |
However, in the table visual, I need to display the data as follows:
Column 1 Column 2 Column 3 Column 4 Column 5
| Value A1 | Value B1 | Value C1 | Value D1 | 10000 |
| 10001 | ||||
| 10002 | ||||
| 10003 |
I've added the columns to the table visual, but the values are being summarized by default, and I can't see an option to display them without summarization. Here’s what I’ve tried so far:
- Added all columns to the table visual.
- Set the numeric column to "Don't Summarize".
Despite these steps, the data still shows only the columns without values or summarizes the numeric field. What steps should I follow to display each row of data without summarizing?
Thank you for your assistance!
Irwan ,
Thank you, Irwan, for your alternative solution. Your approach of unpivoting the columns in Power Query and then using DAX to make repeated values blank seems promising.
- Unpivoted the columns in Power Query to merge them into one column.
- Created a calculated column using your provided DAX formula to conditionally blank out repeated values.
- Plotted the data using a matrix visual.
This method accommodates my multiple column data and simplifies the process. However, as you mentioned, the row layout might need adjustment, but it’s a great start!
Thank you again for your assistance. I will try this approach and keep you posted on the results.
6 Replies
IrwanSuper User
Thank you, Irwan, for your suggestion. I have already applied the "Don't Summarize" option for the last numeric column, but my goal is to display the first four columns with unique values in a single row without repeating the same values across multiple rows.
Here’s an example of my desired output:
Currently, when I add all columns to the table visual and set the numeric column to "Don't Summarize," I still
see each row repeated with the same values in the first four columns.Could you please advise on how to achieve this display where the first four columns show unique values in a single row without repetition?
Thank you for your assistance.
- Irwan
Hello PBI_learn_NK
not sure if this is the most efficient way, but here is the easiest way that i can think.
Also I added some value in your table just incase you need to differentiate value in those column (1-4).
Since you want to put value in all column at the lowest value of Column5, then you need conditional if to make them blank. Tabel visual will show all value in your column and matrix visual seems not match to your need.
Column 1 =var _Value = 'Table'[Column1]var _LowestValue =MINX(FILTER('Table','Table'[Column1]=_Value),'Table'[Column5])ReturnIF('Table'[Column5]=_LowestValue,'Table'[Column1],"")_Value is used for defining value in Column1_LowestValue is used for looking the lowest value of Column5 with exact same value in Column1
Conditional if is used for making blank value for every same string in Column1 (the logic is if the value in Column5 is not matching to the lowest value of Column5 then it is blank).Last step is do this for Column2, Column3, and Column4. Just copy from Column1 and change Column1 into working column (let say you are working on Column2 then change Column1 into Column2).As I said, this might not be the most efficient way, other Power BI expert might have another idea how to achieve your need.
Hope this will help you.Thank you.