Problem to visualize data in table diagram

[MbProg]

Hello,

I have the following data in my Power BI Dataset:

Now I want to visualize this data in a table diagram like this:

But the point is, I don't want to pivot or unpivot the dataset, because I have already unpivoted it to get this data for other reports and I need this structure

How can I accomplish this?

[v-shex-msft]

Hi MbProg,

I agree with ankitpatira’s point and I’d like to share the detail steps:

1. Create the test table.

 

1. Create a Matrix report, drag ‘Company Name’ to ‘Rows’ column, ‘Data’ to ‘Columns’ column set mode to ‘Date Hierarchy’ and clear other detail date fields, ‘Sales Amount’ to ‘Values’ column.

 

Result:

Notice: if you don’t want the total fields, you could close it at format tab.

 

Regards,

Xiaoxin Sheng

[MbProg]

@ankitpatira @v-shex-msft

Your solution works great. There is just one more point:

Now I have the following structure

                      2012    2013

Company X     200       210

Company Y     150       200

I want to have two extra columns like this

                      2012    2013     Diff(Value of 2013 - Value of 2012)        Diff %

Company X     200       210       10                                                            5

Company Y     150       200       50                                                            30

Do you know a way to accomplish this?

[ankitpatira]

@MbProg

In power bi desktop via query editor add custom column and simply subtract 2012 column from 2013 and rename new custom column.

[MbProg]

@ankitpatira

Sorry, but I don't see a way to do that with a custom column. Imagine we have the following data:

                      2012    2013

Company X     230       130

The diff is now 100 and % is 56.5

How can I get that with a custom column?

How would the custom column look like?

[v-shex-msft]

Hi MbProg,

Based on my testing, It is impossible to modify the matrix to your goal.

>> How can I get that with a custom column?

You could follow below steps:

1. Merge records to a new table.

Source table:

 

Measure: (merge the records)

Total Sales Amount = CALCULATE( SUM('Sales Record'[Sales Amount]),FILTER(ALL('Sales Record'),SUMX(FILTER('Sales Record',YEAR('Sales Record'[Date])=YEAR(EARLIER('Sales Record'[Date])) &&'Sales Record'[Company Name]=EARLIER('Sales Record'[Company Name])),'Sales Record'[Sales Amount])))

Dax: Table = DISTINCT( SELECTCOLUMNS('Sales Record',"Company",'Sales Record'[Company Name],"Date",YEAR('Sales Record'[Date]),"Amount",[Total Sales Amount]))

2. Get the diff.

Calculate column:

Diff =

VAR MinYear = MIN('Table'[Date])

VAR MaxYear = MAX('Table'[Date])

var minvalue = LOOKUPVALUE( 'Table'[Amount], 'Table'[Date], MinYear, 'Table'[Company], 'Table'[Company] )

var maxvalue = LOOKUPVALUE( 'Table'[Amount], 'Table'[Date], MaxYear, 'Table'[Company], 'Table'[Company] )

RETURN

(

minvalue- maxvalue

)

 

3. Calculate the diff percent.

diff Pect =

VAR MinYear = MIN('Table'[Date])

VAR minvalue = LOOKUPVALUE( 'Table'[Amount], 'Table'[Date], MinYear, 'Table'[Company], 'Table'[Company] )

return

ABS( [Diff]/minvalue)

 

Regards,

Xiaoxin Sheng

[MbProg]

Hello @v-shex-msft

I have found another way to realize that with table diagram without changing in the Query Mode: http://stackoverflow.com/questions/39123026/visualize-data-in-power-bi-as-matrix/39128551#39128551

Your solution is also great.

My question: what do you recommend: Your solution by chaning the query and show in matrix or just add measures and display it in tables? Which is better regarding to the performance?

[v-shex-msft · Accepted solution]

Hi MbProg,

>> what do you recommend: Your solution by chaning the query and show in matrix or just add measures and display it in tables? Which is better regarding to the performance?

In my opinion, alejandro’s solution is easier, but it needs to fix the year value in the measure. If you use my method, you needn’t to worry about it. As in my measures, it returns maximum and minimum years of current table automatically. Besides, after improving my method, you can also display data in a matrix properly. Please refer to below steps:

1. Use selectcolumns function to format the records.

 

Dax:

Table2= UNION(

SELECTCOLUMNS( DISTINCT( ALLEXCEPT('Table','Table'[Amount],'Table'[Date])),"Company Name",[Company],"Date","diff","Sales Amount",[Diff]),

SELECTCOLUMNS( DISTINCT( ALLEXCEPT('Table','Table'[Amount],'Table'[Date])),"Company Name",[Company],"Date","diff pect","Sales Amount",[diff Pect]))

2. Use union function to merge these records to old table.

Dax:

Table 2 = UNION(SELECTCOLUMNS('Sales Record', "Company Name",[Company Name],"Date",YEAR([Date]),"Sales Amount",[Sales Amount]),

UNION(

SELECTCOLUMNS( DISTINCT( ALLEXCEPT('Table','Table'[Amount],'Table'[Date])),"Company Name",[Company],"Date","diff","Sales Amount",[Diff]),

SELECTCOLUMNS( DISTINCT( ALLEXCEPT('Table','Table'[Amount],'Table'[Date])),"Company Name",[Company],"Date","diff pect","Sales Amount",[diff Pect]))

)

3. Create a matrix visual with above table.

Regards,

Xiaoxin Sheng

[ankitpatira]

@MbProg Use matrix visual with company as rows, date as columns and number as Values.