Forum Discussion

lisago1978's avatar
lisago1978
Helper III
6 years ago
Solved

Columns to Rows in Summary Table

I am trying to take a series of dichotomous colum ns (y/n) and create a summary table where these become rows in either Power BI or Power Query. The table should be dynamic in that the totals will...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi lisago1978 ,

    If you don't want to break the original structure of table, you can create category dimension table first. Then create a measure to get the count of per category. The specific details as follow screen shot:

    1. Create category table

    2. Create a measure with the below formula

    CountofCategory = 
CALCULATE (
    DISTINCTCOUNT ( 'inquiry'[Inquiry] ),
    FILTER (
        ALL ( 'inquiry' ),
        SEARCH ( MAX ( 'Category'[Category] ), 'inquiry'[Category], 1, 0 ) > 0
    )
)

    Best Regards

    Rena

lisago1978's avatar
lisago1978
Helper III
6 years ago

I need to take the first table called data table and create the second table (summary table) in Power Query or Power BI. I think I likely need to create a calculated table perhaps using the summarize function but in addition, I need to transpose the columns into rows.

edhans's avatar
edhans
Community Champion
6 years ago

This is what Power Query was designed for. Please provide sample data using the instructions in the links below, then let me know how you want it summarized before it loads in to DAX. Power Query will do all of the grouping and summarizing you need.

 

You can include a screenshot of what you want the expected result to look like, but don't provide a screenshot of the source data. Use the "sample data" link below.

 

 

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum