How to swap columns and values in matrix

[rahul-opp]

Hello
I am using Matrix to show data.
for example, 2 metrics Sales and Ad Spend with days data.
Currently it look like

Setting:
Rows = Product name
Columns = Date
Values = Sales, Ad Spend ( values could be more like conversion, ACoS )

But we want to show data as following


Is it possible to show data as per second image, I mean main metrics will be on top and dates below each metric? How?

[rahul-opp]

I have created parameter with fields Page Views and clicks and added it in Columns and below that added Date
but it is showing 
ParamMeasure = {
("Clicks", NAMEOF('Test Table'[Clicks]), 0),
("Page Views", NAMEOF('Test Table'[Page Views]), 1)
}

Here is how it appeared.


I have started learning Power Bi recently. So correct me if I made any implementation mistake.

From my initial question, in Matrix I was able to show "Date"( added to columns ) on top and below each date, fields / measures are appearing by adding them to values ( First image )

But I want reverse it where "Dates" should appear below each measure/field.  ( Second Image )

Are there any other ways to create the view?

[MFelix]

Hi @rahul-opp ,

Just change the order of the columns place first the dates and then the parameters

[rahul-opp]

Hi @MFelix , I already tried that, but no use. This is how it looks.


I am able to create first image in my question by simply placing fields as below ( which I don't want ), But I want show "fields" on top and whichever date range under each "field".


There has to be some trick. 

[MFelix]

Hi @rahul-opp ,

Try and create the following measure:

Measure 3 = SWITCH(SELECTEDVALUE(Parameter[Parameter Order]), 0, [Measure], 1 , [Measure 2])

The values of the measures should match the ones in the the parameter table:

Now the table should be setup like this:

[rahul-opp]

@MFelix While creating "ValueMeasure" getting error
The value for 'Page Views' cannot be determined. Either the column doesn't exist, or there is no current row for this column.
Following is measure which I created

ValueMeasure = SWITCH(SELECTEDVALUE(Parameter[Parameter Order]), 0, [Page Views], 1 , [Clicks])

[Page Views] and [Clicks] are correct from Parameter table.

[MFelix]

Page views and click are measures correct? 

[rahul-opp]

Hi @MFelix yes they are measures

[MFelix]

Sorry for the question but you are creating a measure correct not a table? 

[rahul-opp]

Hi @MFelix , Yes I have created a measure which is "ValueMeasure"
I have created same as how you have shown. Something must be missing at my end.
Is it possible to share your pbix file to cross verify?

Attaching images for clarifications



 

[MFelix · Accepted solution]

Hi @rahul-opp ,

Your problem is that this are column on your model and not measures.

Change the Values measure to:

ValueMeasure =
SWITCH (SELECTEDVALUE(Parameter[Paramete Order]),
0, SUM('Master ASIN Join'[Page View]),
1, SUM('Master ASIN Join'[Clicks]))

Should work.

[rahul-opp]

Thanks @MFelix  🙌 It worked. It's good learning.
Further I will try to add addtional stuff like dropdown filter with daily, weekly, quarterly.

Will add another reply to this thread with detailed settings. 
I have created a sample pbix file but there is no option to upload it.

[rahul-opp]

This dummy data


Create parameter as suggested.


Create Measure as suggested. In my case fields, Page Views and Clicks were from model ( not measures ), so used @MFelix 's last suggestion of measure creation.

After adding parameter to "Columns", click on dropdown arrow of Parameter added in Columns
and select "Show selected field" and then click on drill down arrow.

[MFelix]

Don't forget to accept as solution answer so that it can help others

[rahul-opp]

Also found similar questions which uses Unpivot approach, which I also tried , but didn't worked for me. Also Unpivoting existing columns which I want to use in Matrix, will creatre issue as same dataset, I am or I will use in other reports where this unpivoting might not be needed.

Your approach looks good as it only do changes on DAX and visual level. 

https://community.fabric.microsoft.com/t5/Desktop/Swap-Matrix-visualization-Columns-Values/m-p/14099... 
https://community.fabric.microsoft.com/t5/Desktop/Matrix-Change-quot-Level-quot-of-columns-amp-value... 

[MFelix]

Hi @rahul-opp ,

Create a parameter field with the two measures you need then on the columns add the parameter and then the date this will allow to show the values on that hierarchy level.

https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters

[rahul-opp]

@MFelix Thanks for reply. I will check the parameter part and update here