I need to attach a new column to the matrix visualization.

[Anonymous]

Hi,

I'm very new to this community, Please help me out here. My task is to create a matrix visualization every week and get the total counts. I want to have this total count column to get attached to next week's matrix visualization as naming the previous week's total and I need to create a new column that has the difference between the Total and the previous week's total. The rows should match the previous week's total and new total columns in the matrix visualization.

Thanks

Govind Daga

[Anonymous]

@v-yanjiang-msft  Will this work with the pivot table / Matrix Visualization? Because I need to match the rows of this week with the previous week's rows too.

[v-yanjiang-msft]

Hi @Anonymous ,

Sorry I'm not very clear about your expected result "match the rows of this week with the previous week's rows". Could you please give a simple example about your expected result? You can show it in an Excel or something else.

Best Regards,
Community Support Team _ kalyj

[Anonymous]

@v-yanjiang-msftHere is the resulting image that I want to have. The previous week may or may not have certain alphabets, for example, if you see under production in 'k' we don't have it in the previous week so that's the reason we put blank(-). I'm creating a pivot table on the power bi but am unable to join the previous week's total with the current week's total. 

Can I create a measure in pivot table/matrix visualization in PowerBI? If yes what should the DAX for it be?
Note: Previous week and Difference are being done manually.

Thanks for your reply. Appreciate it.

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

In Power BI, Matrix visual is not the same with it in Excel. Matrix visual has three fileds, Rows, Columns and Values.

The Grand total column can easily get by turning on the total option in the format pane. But if you want to get the below result in a matrix in Power BI, it's going to be a big change to the data model. Values in the red box should be in a column placed in Rows in matrix visual, values in the green box should be in a column placed in Columns in matrix visual, and all the values in yellow box can be a column or measure placed in Values in matrix.

So if your sample is not in this format, you should first modify it in Power Query or create a new table manually like this:

In this sample, put domain in Rows, Column1 and Column2 in Columns.

For the Values part of the matrix, create a measure manually calculate its value according to the Columns by the SWITCH function like this:

Measure =
SWITCH (
    MAX ( 'Table'[Column1] ),
    "Within KPI", 1,
    "Grand Total", 2,
    "Previous Week", 3
)

The 1, 2, 3 in the formula is just an example, it should be calculated based on the corresponding value in the fact table.

Best Regards,
Community Support Team _ kalyj

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

[Anonymous]

Hi @v-yanjiang-msft , I got the columns until the grand total in Power BI too, but my challenge is to get the previous week's total and attach it beside the current week's Grand Total value and of course also the difference.

[v-yanjiang-msft]

Hi @Anonymous ,

I know you can get the grand total, the workaroud I mentioned in the previous post is how to get the previous week's total in the same visual. By default, it can't be achieved, you need to create a new table to get the expected result.

Best Regards,
Community Support Team _ kalyj

[v-yanjiang-msft]

Hi @Anonymous ,

According to your description, I create a sample.

Based on the sample, I create a matrix like below, put the Week column in a slicer and put Week and Sales column in a matrix.

Create two measures.

PreviousWeek =
CALCULATE ( SUM ( 'Table'[Sales] ), 'Table'[Week] = MAX ( 'Table'[Week] ) - 1 )

Diff = SUM('Table'[Sales])-[PreviousWeek]

Get the result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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