CODEWITHSUNDEEP
powerbidaxpowerquery
deoos
deoos

Reputation: 49

Power BI. Matrix two columns under one column

This is the desired layout of the matrix that I need. I need to have two columns under one column:

Desired layout

However, I ended up with this(I used different values, so the values might be slightly different.):

enter image description here

With the following columns in the matrix fields:

enter image description here

This is a sample of the dataset. Total Units sum up columns A and B. The Order column sorts the Status column: 

STATUS  A        B     Total Units  Order 
ABC     3        0     3            1
DEF     0        6     6            2
ABC     3        2     5            1
ABC     5        6     11           1
GHI     0        4     4            3
ABC     5        3     8            1
DEF     0        9     9            2

How do I get my desired layout? Do I need to pivot the table? Or do I need to group certain values together?

Upvotes: 1

Views: 18896

Answers (1)

RADO
RADO

Reputation: 8148

First, transform your data to this:

enter image description here

Changes I've made:

  • Unpivoted source data for columns A, B, and renamed resulting column as "Unit Type"
  • Deleted column "Total Units", it's unnecessary
  • Named your table "Data" (you can change it to whatever name you prefer).

Then, write 2 DAX measures.

Measure 1:

Units = SUM(Data[Value])

Measure 2: 

% = DIVIDE ( [Units], CALCULATE ( [Units], ALL ( Data[STATUS] ) ) )

Then, create a matrix as follows:

enter image description here

Result:

enter image description here

Explanation:

  • You need to unpivot data to put A and B types into the same column. This will allow you to use them to slice data as matrix rows;
  • Total Units is a useless column because we can easily reproduce it with DAX measure, and then it will be dynamic (it will respond to matrix rows, columns, slicers and other interactive controls);
  • % formula needs to calculate % of units for a given unit type, but across all Status. We accomplish that by first calculating subtotal units for ALL(Data[STATUS]), and then dividing units by them.

Upvotes: 3

Related Questions