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
Anonymous
Not applicable

How to pivot a table (created using DAX-SUMMARIZED, not Power Query) on a column?

To All awesome Power BI users,

 

I have created a summarized table referring other tables and their field using DAX SUMMARIZE in Power BI desktop and summarized the Total amount in Dollars $. I see my created table in Data Pane and it looks like this:
From this.JPG

 

But, I want to show it to user in the following format:
To This.JPG

 

As this is the summarized table that is created using DAX, how to I chnage its format to look this the desired one? Any help would be appretiated. 

 

Thanks,

Abi

1 ACCEPTED SOLUTION
v-yueyunzh-msft
Community Support
Community Support

Hi, @Anonymous 

According to your description, you want to  pivot a table (created using DAX-SUMMARIZED, not Power Query) on a column. Right?

Here are the steps you can follow:

(1)This is my test data:  Test

vyueyunzhmsft_0-1662531562370.png

 

(2) We can click New Table and enter DAX :

Table = ADDCOLUMNS(  SUMMARIZE('Test','Test'[GATEGORY]) ,

"2019" , CALCULATE( SUM('Test'[TOTAL]) , TREATAS( {2019},Test[YEAR])),    

"2020" , CALCULATE( SUM('Test'[TOTAL]) , TREATAS( {2020},Test[YEAR])),

"2021" , CALCULATE( SUM('Test'[TOTAL]) , TREATAS( {2021},Test[YEAR])),

"2022" , CALCULATE( SUM('Test'[TOTAL]) , TREATAS( {2022},Test[YEAR]))

)

 

(3)Then we can meet your need , the result is as follows :

vyueyunzhmsft_1-1662531562375.png

 

And according to what you said later, if you want to display the corresponding effect in the Matrix visual object, you can hide the previous [GATEGORY] in the following ways:

(1)We can close wrap function in "Format" pane:

vyueyunzhmsft_2-1662532047325.png

(2) We can hover over our header and drag to hide the fields for our column:

vyueyunzhmsft_3-1662532127159.png

 

If this method cannot help you solve the problem, you can delete your private data and provide your sample data in the form of a table or .pbix file, and describe your detailed needs, so that we can better help you solve the problem.

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-yueyunzh-msft
Community Support
Community Support

Hi, @Anonymous 

According to your description, you want to  pivot a table (created using DAX-SUMMARIZED, not Power Query) on a column. Right?

Here are the steps you can follow:

(1)This is my test data:  Test

vyueyunzhmsft_0-1662531562370.png

 

(2) We can click New Table and enter DAX :

Table = ADDCOLUMNS(  SUMMARIZE('Test','Test'[GATEGORY]) ,

"2019" , CALCULATE( SUM('Test'[TOTAL]) , TREATAS( {2019},Test[YEAR])),    

"2020" , CALCULATE( SUM('Test'[TOTAL]) , TREATAS( {2020},Test[YEAR])),

"2021" , CALCULATE( SUM('Test'[TOTAL]) , TREATAS( {2021},Test[YEAR])),

"2022" , CALCULATE( SUM('Test'[TOTAL]) , TREATAS( {2022},Test[YEAR]))

)

 

(3)Then we can meet your need , the result is as follows :

vyueyunzhmsft_1-1662531562375.png

 

And according to what you said later, if you want to display the corresponding effect in the Matrix visual object, you can hide the previous [GATEGORY] in the following ways:

(1)We can close wrap function in "Format" pane:

vyueyunzhmsft_2-1662532047325.png

(2) We can hover over our header and drag to hide the fields for our column:

vyueyunzhmsft_3-1662532127159.png

 

If this method cannot help you solve the problem, you can delete your private data and provide your sample data in the form of a table or .pbix file, and describe your detailed needs, so that we can better help you solve the problem.

 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you for this. I tried running it, but after the TREATAS, I get the error that it failed to resolve name 'WETCH'.

My variables are slightly different, and instead of year, I have WETCH and PRED data. So my long table looks like:

Variable   Type         Value

Beef         WETCH     80

Beef         WETCH     75

Beef         PRED        82

Beef         PRED        76

 

And I would like the final table to look like this:

Variable  PRED  WETCH

Beef        79       77.5

 

I have many more variables besides just Beef though.

 

Here is my code I was trying:

table = 

ADDCOLUMNS( SUMMARIZE('Main','Main'[Variable]) ,
"WETCH" , CALCULATE( AVERAGE('Main'[Value]) , TREATAS( {WETCH},Main[Type])),
"PRED" , CALCULATE( AVERAGE('Main'[Value]) , TREATAS( {PRED},Main[Type])),)

 

The red error lines come on the word just after the "TREATAS", so "WETCH" and "PRED" between the {} brackats.

 

Any ideas of what I'm doing wrong? Thank you.

For anyone reading this thread and having the same problem as @neuro_fun (and me!) with the TREATAS function, you need to put the names in the {} brackets in between ""

Okay, that worked. I changed my code to:

table = 

ADDCOLUMNS( SUMMARIZE('Main','Main'[Variable]) ,
"WETCH" , CALCULATE( AVERAGE('Main'[Value]) , TREATAS( {"WETCH"},Main[Type])),
"PRED" , CALCULATE( AVERAGE('Main'[Value]) , TREATAS( {"PRED"},Main[Type])))

 

 

Okay, so another question. Is there a way to only show rows with both WETCH and PRED having populated values? So in that table, if one of the values are blank, have the row be empty.

Thank you!!

amitchandak
Super User
Super User

@Anonymous , Use Matrix visual , Category on Row, Year on column and Total on values

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-matrix-visual

 

or Unpivot data

https://radacad.com/pivot-and-unpivot-with-power-bi

Anonymous
Not applicable

@amitchandak ,Thanks for suggesting the solution. 

As I have created this table using DAX SUMMARIZE, it cannot be altered in Power Query. 
I have tried Matrix Visual like you suggested. Ultimately, what I want is something like this:TOTHIS.JPG

What I have right now is three individual matrix like this:
FROMTHIS.JPG

How this can be achieved ? 

Thanks,

Abi

Helpful resources

Announcements
Europe Fabric Conference

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.

Top Solution Authors