am I overcomplicating this? I cannot figure it out.

[jmdaily83]

My client is asking that I first replicate what they've done in Excel and then we will move on to other things.  They have a very simple table as shown below listed out by project.  I'm struggling with the red section. I know I can do it measure by measure but quite frankly I do not want to have to do 100 measures to achieve something so simple.  I am trying to find a way to put the max, min, ct, avg as rows and the column headers as headers.  I've tried unpivoting, transposing, etc.    I definitely feel like this is one of those cases where I'm just overcomplicating something and totally missing the obvious, easy solution.

Can someone help?

[jmdaily83]

@jgeddes Would you have any insight on how to maintain the row project numbers while still grouping it?

[jgeddes]

With an example table that looks like...

Unpivot Other Columns based on Project to get...

Load that into the desktop editor.

You will now need to create four measures (I would use MAXX, MINX, AVERAGEX, COUNTX)

Example

Max = 
MAXX('Table',[Value])

and use those measures in a matrix visual. You will now be able to slice based on the project column in the table. You will likely need to build a relationship to the project column in the rest of your model to get the slicing to work as your need.

[jmdaily83]

@jgeddes I'm sorry - I'm not being clear with explaining the end result that I need. I do still need it grouped by the headers below

But I also need to be able to tie it to the projects behind the rows that are behind the calculations so that when a project related slicer is selected, the calculated table will change dynamically

[jgeddes]

Is this similar to your desired result?

[jmdaily83]

@jgeddes yes, and then if you select ABC the values would change above

[jgeddes]

Ok. How is your table currently formatted? Does it look like..

Or is it formatted differently?

[jmdaily83]

It's formatted like you show 

[jgeddes · Accepted solution]

Ok. In Power Query, select the columns that need to remain as rows. (In my example it would be 'BD Opportunity' and 'State'. ) Select 'Transpose Other Columns. You should end up with data formatted like this...

Load that into the desktop editor. 

From the desktop editor you will need to create the four measures I mentioned above (MAXX, MINX, COUNTX, AVERAGEX).

You can create your main table using a matrix visual with 'BD Opportunity' and 'State' and any other rows in the rows fields. Put the 'Attribute' column in the columns field and 'Value' in the values field. You should end up with...

Create a second matrix above that one with your four measures as the values and 'Attribute' as the column. You will need to ensure that 'Switch values to rows' is selected with the matrix format (Values/Options). The last piece is to create a slicer that has the 'BD Opportunity' field.

Hope this helps.

[jmdaily83]

Thank you so much for all of your help

[jmdaily83]

@jgeddes would you have any additional insight regarding adding the project name and having the same result?

[jgeddes]

You should be able to do this in Power Query.

Here is a small example table...

I was able to get the following result...

With this code...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyUNJRMjI1AZKmZiZKsTrRYBrEAxJmphAhM6gCCzBpbqEUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Column", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Column"}, {{"Max", each List.Max([Value]), type number}, {"Min", each List.Min([Value]), type number}, {"Avg", each List.Average([Value]), type number}, {"Count", each Table.RowCount(_), Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Grouped Rows", {"Column"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Column]), "Column", "Value", List.Sum)
in
    #"Pivoted Column"

Basically unpivot all of the columns, group them by the previous headers choosing the four aggregations you are looking for (max, min, avg, count). From there unpivot the aggregated columns then pivot the column with the previous header values choosing the column with the aggregated values as the values column.

Hope this gets you pointed in the right direction.

[jmdaily83]

@jgeddes This worked perfectly - However, I forgot to mention I still need to have things tied to the project number for each attribute. The project numbers won't appear in the newly created table but when other items are filtered on, the min/max/avg/ct need to change accordingly based on the different features of the project. For instance, location, status, length.