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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
IBrant
Frequent Visitor

Multiple Columns as Rows with Multiple Calculations of those Columns

I have some data in columns as seen below:

PBI Table.jpg

 I need this data to appear in a visual like this: 

Excel Table.jpg

 I can't figure out how to make the column headers as rows and then get multiple values from these columns, such as average, count and sum. I have only been able to get a single value like average:

IBrant_0-1706821008962.png

Help, please!

6 REPLIES 6
v-cgao-msft
Community Support
Community Support

Hi @IBrant ,

Please try:
1. unpivot these colunms in powerqery:

vcgaomsft_0-1707185705514.png

2. Group by [Attribute]:

vcgaomsft_1-1707185742879.png

vcgaomsft_2-1707185783782.png

If you want create a matrix visual, please select these and unpivot

vcgaomsft_3-1707185869703.png

vcgaomsft_4-1707185914332.png

vcgaomsft_5-1707186006684.png

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

Hi, Gao,

 

I forgot to mention that these are columns that are calculated based on date columns. So in Power Query the original columns are all dates. I added new columns in table view and wrote formulas to figure out the number of days between the dates and that is how I got those columns.  I apologize, I'm a relatively new Power BI user.

v-cgao-msft
Community Support
Community Support

Hi @IBrant ,

Try selecting the matrix. --> format pane -> Value --> switch values to the row --> row headers --> turn off stepped layout:

vcgaomsft_0-1706857038074.png

vcgaomsft_1-1706857100939.png

Output:

vcgaomsft_2-1706857167246.png

Is this your expected output?

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

Hi, Gao,

 

Thanks for your response but I need it to look more like this: 

IBrant_1-1707141494083.jpeg

Also, I do not have a row column that inclues all of the categories like your country column. TIA

amitchandak
Super User
Super User

@IBrant , You need to create calculation groups, you have option in model view to create calculation groups

Create three calculation items and use measures for each of columns. use the Expression function to use data from the line level

 

Avg = AVERAGEX(Table,SELECTEDMEASURE())
Count = COUNTX(Table,SELECTEDMEASURE())
Sum = Sumx(Table,SELECTEDMEASURE())

 

 

I used these two measure and test

Qty Line = Sum(Sales[Qty])
Gross Line = sum(Sales[Gross Sales])

 

 

amitchandak_0-1706851482641.png

Model explorer public preview with calculation group authoring| Measure Slicer: https://youtu.be/VfxfJJ0RzvU

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

This sounds great, but I don't have a calculation group option.

IBrant_0-1707141393558.png

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.