Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
lbuzz
Frequent Visitor

Calculate a table of Min, Max, and Average across multiple columns

My table contains 4 columns and I am aiming to create a table such that I can show the min, max, avg, and weighted avg of 3 columns(4th column has values that help calculated the weighted average formula.

The rows should say Min, Max, Avg etc and columns should say the Column names. It's like a pivot table in Excel but I'm not able to replicate it in powerBI.
 The end result looks like this table from excel I have attached. Appreciate any guidance on this
Capture.PNG

1 ACCEPTED SOLUTION
Jimmy801
Community Champion
Community Champion

Hello @lbuzz 

 

you can create a new query where you are using Table.Profile to create a new table with the desired data. Here an example code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYlOlWJ1oIKmjZAHEJmCeMZAFwbGxAA==", 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}}),
    TableProfile= Table.Profile(#"Changed Type")
in
    TableProfile

The output of this function you can easily show in a table-visual

Jimmy801_0-1601535815936.png

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

View solution in original post

3 REPLIES 3
Jimmy801
Community Champion
Community Champion

Hello @lbuzz 

 

you can create a new query where you are using Table.Profile to create a new table with the desired data. Here an example code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYlOlWJ1oIKmjZAHEJmCeMZAFwbGxAA==", 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}}),
    TableProfile= Table.Profile(#"Changed Type")
in
    TableProfile

The output of this function you can easily show in a table-visual

Jimmy801_0-1601535815936.png

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Fowmy
Super User
Super User

@lbuzz 

To have the format you desire, you need to use a MATRIX visual and go to Value Property and set the show on Row to ON

 

Fowmy_0-1601493774020.png

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

 

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

lbuzz
Frequent Visitor

Hello thank you so much for the early reply
But if I use a matrix and drop the values with Row selection, I end up seeing a top to bottom structure vs a pivot matrix style. If somehow I could Fix the keywords Min Max,Avg on rows and every new column A,B,C goes into new column, that'd really help me 
Capture.PNG

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors