Custom aggregate column in power bi matrix

Question

I'm trying to create a matrix in a Power BI report summarizing Salesperson's performance sliced in a number of different ways.

I know how to create a matrix with Rows - Salesperson, Columns - Product Type, and Values - count of Sales which will show the number of Sales per Salesperson per Product Type, but I'd like also be able to do the following:

1. Add an additional column set to pivot on (e.g. Sales Year), so that I could see count of Sales pivoted by both Product Type and Year in the same table side by side (i.e., not nested).
2. Add additional summary columns to my matrix showing values such as average Sale Amount by Salesperson, % of the total number of Sales by Salesperson.

For clarity, I'd imagine that this would result in a matrix where the column headers read: Salesperson, Product 1, Product 2, ..., Year 1, Year 2, ..., Total Sales Count, Average Sales Amount, % of Total Sales Count. See image link below (I don't have the reputation points to include the actual image yet)

I recognize that I can do this by creating measures which effectively replicate how the matrix is splitting out the values and adding each measure as a value (no Columns), but I don't want to have to create new measures and update the matrix every year or every time we add a new Product Type.

I've also looked at custom visuals on the Power BI marketplace but didn't see any that would achieve this.

Answer 1

• We need a measure with a SWITCH as well as a table for headers.

• We can also generate a header table along these lines:

Header = UNION(SUMMARIZE(Sales, Sales[Product], "Group", "By Product", "Index", 1), SUMMARIZE(Sales, Sales[Year], "Group", "By Year", "Index", 2), DATATABLE("Header", STRING, "Group", STRING, "Index", INTEGER, {{" Total", "Summarizations", 3}, {"% of Total Sales", "Summarizations", 3}, {"Avg Sale Size", "Summarizations", 3}}))

Which will look like this:

Header, Group, Index, Product 1, By Product, 1, Product 2, By Product, 1, 2016, By Year, 2, 2017, By Year, 2, 2018, By Year, 2, Total, Summarizations, 3, % of Total Sales, Summarizations, 3, Avg Sale Size, Summarizations, 3

• This table will automatically expand when more products or years are added. 

• Once we have that, we just need to put Group and Header on the columns of a matrix visual and Salesperson on the rows, with a switching measure in the values.

Measure = VAR Val = SWITCH(SELECTEDVALUE(Header[Group]), "By Product", CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Product] = MAX(Header[Header]))), "By Year", CALCULATE(SUM(Sales[Amount]), FILTER(Sales, Sales[Year] = VALUE(MAX(Header[Header])))), SWITCH(SELECTEDVALUE(Header[Header]), "% of Total Sales", DIVIDE(SUM(Sales[Amount]), CALCULATE(SUM(Sales[Amount]), ALL(Sales))), "Avg Sale Size", AVERAGE(Sales[Amount]), SUM(Sales[Amount])) ) RETURN IF(SELECTEDVALUE(Header[Header]) = "% of Total Sales", FORMAT(Val, "0.0%"), FORMAT(Val, "0.0"))

• Each different group gets its own calculation and we have to use the FORMAT function to force the table to format the percentage function properly.

Note: If you have slicers or filtering, you probably want to use ALLSELECTED where I used ALL above.

If u want to learn the tool in depth, then come to us and sign up for this Power BI Microsoft certification course online at Intellipaat.