Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
NehaSha
Helper II
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  
startdatesalesInsalesoffWrieoffFtNetRentLocationid
4/1/20191020.5$200$200,0001
4/2/2019800.7$201$201,0001
4/3/2019711$202$202,0001
4/4/2019500.5$203$203,0001
4/5/2019120.9$204$204,0001
4/1/2019940.8$205$205,0002
4/2/2019230.8$206$206,0002
4/3/2019800.8$207$207,0002
4/4/2019410.8$208$208,0002
4/5/2019310.8$209$209,0002
4/1/20181020.8$210$210,0001
4/2/20181550.8$211$211,0001
4/3/2018710.8$212$212,0001
4/4/2018600.8$213$213,0001
4/5/2018310.8$214$214,0001
4/1/2018120.8$215$215,0002
4/2/20181270.8$216$216,0002
4/3/2018320.8$217$217,0002
4/4/2018400.8$218$218,0002
4/5/2018220.8$219$219,0002
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 valuePer_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/2019For Ex User selected 4/4/19      
Location ID :ALL      
Expected Results   
Attribute ActualLYDifference    
salesoff1119-8 -ve red , no need $signcalculation based on b/w startdate and enddate  
salesIn5358-5 -ve red ,no need $signcalculation based on b/w startdate and enddate  
NetRent$1,632,000$1,712,000($80,000) -ve red ,need $sign , no decimal placecalculation based on b/w startdate and enddate  
Writeoff1.33.2-1.9 -ve red , one decimalplacecalculation based on enddate  
PerOcc40.80%44.60%-3.80%need -ve red , %sign ,with  1 decimal as part of 1 matrixcalculation based on b/w startdate and enddate  
Ft$411$431($20) -ve red, nodecimal place but $signcalculation based on  enddate  
Per_NetRent$7642$7328$314need -ve red , %sign ,with  1 decimal as part of 1 matrixcalculation 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
KBO
MVP

As mentioned above:

1) Go to "Edit Queries"

2) Select all Colums

3) Transform: Unpivot Columns

 

S1.pngS2.png

 

Best Kathrin

View solution in original post

2 REPLIES 2
KBO
MVP

As mentioned above:

1) Go to "Edit Queries"

2) Select all Colums

3) Transform: Unpivot Columns

 

S1.pngS2.png

 

Best Kathrin

v-yulgu-msft
Employee
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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.