cancel
Showing results 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

Helper II

## Matrix show on rows

Hi All,

Need help for the below mentioned scenario. I treied one solution but couldnt resolve formatting for each columns as expected and sorting acc. to Expected result, along with Percentage column in one matrix:

Need to calculate This year, Last Year and Difference TY-LY from Table daily load based on Timeline slicer selection.

Few columns are calculated between startdate and enddate, few based on only startdate and few calculation based on only end date. Thanks in advance!

 Need help for  the below mentioned sample data :data loaded  from 2011 till yesterday date for each day for each location startdate salesIn salesoff Wrieoff Ft NetRent Locationid 4/1/2019 10 2 0.5 \$200 \$200,000 1 4/2/2019 8 0 0.7 \$201 \$201,000 1 4/3/2019 7 1 1 \$202 \$202,000 1 4/4/2019 5 0 0.5 \$203 \$203,000 1 4/5/2019 1 2 0.9 \$204 \$204,000 1 4/1/2019 9 4 0.8 \$205 \$205,000 2 4/2/2019 2 3 0.8 \$206 \$206,000 2 4/3/2019 8 0 0.8 \$207 \$207,000 2 4/4/2019 4 1 0.8 \$208 \$208,000 2 4/5/2019 3 1 0.8 \$209 \$209,000 2 4/1/2018 10 2 0.8 \$210 \$210,000 1 4/2/2018 15 5 0.8 \$211 \$211,000 1 4/3/2018 7 1 0.8 \$212 \$212,000 1 4/4/2018 6 0 0.8 \$213 \$213,000 1 4/5/2018 3 1 0.8 \$214 \$214,000 1 4/1/2018 1 2 0.8 \$215 \$215,000 2 4/2/2018 12 7 0.8 \$216 \$216,000 2 4/3/2018 3 2 0.8 \$217 \$217,000 2 4/4/2018 4 0 0.8 \$218 \$218,000 2 4/5/2018 2 2 0.8 \$219 \$219,000 2 Report contain 2 slicer Timelineslicer and LocationID Slicer startdate will always be first of that month PercentageOcc =sum salein between startdate and enddate / writeoff basedon timelineslicer end date value Per_NetRent=sum(NetRent) on First of seleceted month /sales in between startdate and enddate Please help in how to calculate actual , LY and difference column based on timeline slicer selection and how to define data in category actual ,LY,difference Please help how to represent the data in the form of matrix table which will show data on rows as shown in example Timesline Slicer contain EndDate for Timeline Slicer 4/4/2019 For Ex User selected 4/4/19 Location ID :ALL Expected Results Attribute Actual LY Difference salesoff 11 19 -8 -ve red , no need \$sign calculation based on b/w startdate and enddate salesIn 53 58 -5 -ve red ,no need \$sign calculation based on b/w startdate and enddate NetRent \$1,632,000 \$1,712,000 (\$80,000) -ve red ,need \$sign , no decimal place calculation based on b/w startdate and enddate Writeoff 1.3 3.2 -1.9 -ve red , one decimalplace calculation based on enddate PerOcc 40.80% 44.60% -3.80% need -ve red , %sign ,with  1 decimal as part of 1 matrix calculation based on b/w startdate and enddate Ft \$411 \$431 (\$20) -ve red, nodecimal place but \$sign calculation based on  enddate Per_NetRent \$7642 \$7328 \$314 need -ve red , %sign ,with  1 decimal as part of 1 matrix calculation based on 1st of the month (based on end date month selection ) sorting attribute in user requested order there is no asc or desc criteria

Thanks, Neha

1 ACCEPTED SOLUTION
MVP

As mentioned above:

1) Go to "Edit Queries"

2) Select all Colums

3) Transform: Unpivot Columns

Best Kathrin

2 REPLIES 2
MVP

As mentioned above:

1) Go to "Edit Queries"

2) Select all Colums

3) Transform: Unpivot Columns

Best Kathrin

Microsoft Employee

Hi @NehaSha ,

You could unpivot table in Query Editor mode to transform columns into rows.

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### 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 Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors