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

[Anonymous]

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:

But, I want to show it to user in the following format:

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

[v-yueyunzh-msft · Accepted solution]

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’

(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 :

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:

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

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.

[neuro_fun]

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.

[issya]

Hi, do you have any suggestions on how to do this the opposite way. I have data like your output and would like to take it to the form of your input dataset. appreciate any help

[tgjones43]

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 ""

[neuro_fun]

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]

@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]

@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:

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


How this can be achieved ? 

Thanks,

Abi