Forum Discussion

NehaSha's avatar
NehaSha
Helper II
7 years ago
Solved

Matrix Table Transpose Rows represent column and Column represent Rows with multiple data type $,%

Hello everyone!

 

Need help to show Matrix Table Transpose

 

 NetRentTYGrossPotAuction PercentageTotalArea
Actual $500,000$510,55039%1000
LY$500,000$510,55040%900

 

to 

 Actual LY
NetRent$500,000$500,000
GrossPot$510,550$510,550
Auction Percentage39%40%
TotalArea1000900
   

 

with $sign,%sign,Comma between numbers and Whole Numeric Number or Decimal Number 

 

Thanks in advance!

 

Thanks,

Neha

Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    Hi NehaSha ,

     

    Not sure how you have your data setup but if your matrix table looks like the first one you show, select the options on the matrix visual and go to values, then turn on the option Show on rows, the values that you have as values will appear in rows instead of columns.

     

    Be aware that your Actual / PY column needs to be in columns and not on rows.

     

    Regards,

    MFelix

  • MFelix's avatar
    MFelix
    7 years ago

    Hi NehaSha ,

     

    You can add a column to each table refering to  Category (Acctual / PY), then rename the columns on both table to the same name and make an append of the data into a single table, then you can use  the solution I gave.

     

    Regards,

    MFelix

  • MFelix's avatar
    MFelix
    7 years ago

    Hi NehaSha ,

     

    Looking at your data maybe the best would be to make structural changes to your data in order to have it in a different shape. However I know that sometimes that is difficult to do because you already have a lot done in your work.

     

    Let's create the following measures:

    Net Rent = SUM(Power_Query[NetRentTY])

Gross pot = SUM(Power_Query[GrossPot])

Total Area = SUM(Power_Query[TotalArea])

Auction percentage% = 
IF (
    SELECTEDVALUE( Power_Query[Type] ) = "Var Difference";
    CALCULATE ( [Net Rent]/[Gross pot]; Power_Query[Type] = "Actual" )
        - CALCULATE ( [Net Rent]/[Gross pot]; Power_Query[Type] = "LY" );
   [Net Rent]/[Gross pot]
)

    Now use this measures on your matrix and choose on the values options Show on Rows.

     

    This will allow to select more than one value on your slicer for location.

     

    Check PBIX file attach.

     

    Regards,

    MFelix

16 Replies

  • MFelix's avatar
    MFelix
    Super User
    7 years ago

    Hi NehaSha ,

     

    Not sure how you have your data setup but if your matrix table looks like the first one you show, select the options on the matrix visual and go to values, then turn on the option Show on rows, the values that you have as values will appear in rows instead of columns.

     

    Be aware that your Actual / PY column needs to be in columns and not on rows.

     

    Regards,

    MFelix

    • NehaSha's avatar
      NehaSha
      Helper II
      7 years ago

      Hi MFelix ,

       

      Thanks for your reply! 

       

      My bad I didnt give right example in previous email ! I thought it will be easy for me to get the data in matrix form but cant not replicate that.

       

      But below is the real table example  Query1 Represent

       

      NetRentTYGrossPotAuction PercentageTotalAreaLocationID
      $500,000$510,55039%10001

       

      Query2 Represent 

       

      NetRentLYGrossPotLYAuction PercentageLYTotalAreaLYLocationID
      $410,550$413,00020%900.08

      1

       

      Need Result:

       

       Actual LY
      NetRent$500,000$410,550
      GrossPot$510,550$413000
      Auction Percentage39%20%
      TotalArea1,000900.08

       

      Please help with this real scenario !

       

      Thanks,

      Neha

      • MFelix's avatar
        MFelix
        Super User
        7 years ago

        Hi NehaSha ,

         

        You can add a column to each table refering to  Category (Acctual / PY), then rename the columns on both table to the same name and make an append of the data into a single table, then you can use  the solution I gave.

         

        Regards,

        MFelix

    • nardtmo's avatar
      nardtmo
      Helper III
      6 years ago
       

       

      Hi,

      appreciate the help in advance. but facing similar issue. tried th pbix file submitted in this tread but getting some error 'UNION' must have the same number of columns. Not sure if it has something to do with # of columns i have on this report or maybe got the dax formula wrong.

       

      anyway.  see attached pic. i got multilple columns but want to create a matrix table with "RF review summary" content as column and the sum of final 1 ...to final 9 as row.

       

      thanks again.

       

      Nard

       

  • NehaSha's avatar
    NehaSha
    Helper II
    7 years ago

    Hi MFelix ,

     

    Thanks for sharing both ideas!

    Please see the below mentioned scenarios:

    1. Option

    POWER_QUERY > EDIT Query > Pivot UnPivot 

    With One LocationID Percentage column is coming right but with multiple selection of LocationID percentage columns are not coming correct 

    NetRentTY

    GrossPot

    Auction Percentage

    TotalArea

    LocationID

    		 

    $500,000

    $510,550

    97.93%

    1000

    1

    		 

    $300,000

    $350,000

    85.71%

    850

    2

    		 

    $400,000

    $450,000

    88.89%

    900

    3

    		 

    NetRentLY

    GrossPotLY

    Auction PercentageLY

    TotalAreaLY

    LocationID

    		 

    $410,550

    $413,000

    99.41%

    900.08

    1

    		 

    $315,000

    $329,000

    95.74%

    700

    2

    		 

    $372,000

    $390,000

    95.38%

    870

    3

    		 

     

     

     

     

     

    		 

    For percentage Column:  sample Formula used NetRent/GrossPot

    		 
          

     

    Actual 

    LY

    Diiference

    		  

    NetRent

    $1,200,000

    $1,097,550

    $102,450

    		Worked 

    GrossPot

    $1,310,550

    $1,132,000

    $178,550

    		Worked 

    Auction Percentage

    91.56%

    96.96%

    -5%

    		Need Solution 

    TotalArea

    2750

    2470.08

    279.92

    		Worked 

    But Right now result is for Percentage Column SUM of all Percentage

    		  

    Auction Percentage

    272.54%

    290.54%

    -18%

    		  

    OR Result came Divide by 100

    		  

    Auction Percentage

    2.73%

    2.91%

    -0.18%

    		  

    The result is not working for Percentage columns based on multiple locationID.

     

    I have slicerFilter which contain multiple groups of different locations . based on Group selection need the Result. For one LocationID result is correct for Percentage column but not for multiple LocationID.

     

    2. Option DAX Query :

    I have created DAX TAble 

    which shows MAX of LocationID  , based on that result is always Total of all Locations , not working for the filter Location Groups.  

     

    It will be very helpful, Please suggest the Option1 changes, if you have lack of time ! In Option 1 Power Query, I tried by added custom column and use the formula NetRent/GrossPotRent but this also giving same result.

     

    Thank you very much in advance !

     

    Thanks

    Neha

    • MFelix's avatar
      MFelix
      Super User
      7 years ago

      Hi NehaSha ,

       

      Looking at your data maybe the best would be to make structural changes to your data in order to have it in a different shape. However I know that sometimes that is difficult to do because you already have a lot done in your work.

       

      Let's create the following measures:

      Net Rent = SUM(Power_Query[NetRentTY])

Gross pot = SUM(Power_Query[GrossPot])

Total Area = SUM(Power_Query[TotalArea])

Auction percentage% = 
IF (
    SELECTEDVALUE( Power_Query[Type] ) = "Var Difference";
    CALCULATE ( [Net Rent]/[Gross pot]; Power_Query[Type] = "Actual" )
        - CALCULATE ( [Net Rent]/[Gross pot]; Power_Query[Type] = "LY" );
   [Net Rent]/[Gross pot]
)

      Now use this measures on your matrix and choose on the values options Show on Rows.

       

      This will allow to select more than one value on your slicer for location.

       

      Check PBIX file attach.

       

      Regards,

      MFelix

      • NehaSha's avatar
        NehaSha
        Helper II
        7 years ago

        Thank you very much !It worked !

        :smileyhappy:

         

        Thanks Neha

    • NehaSha's avatar
      NehaSha
      Helper II
      7 years ago

      Hi MFelix ,

       

      Thanks for your help in previous example !  

      Actually in previous example i was passing automate start date (first of current month) and enddate (yesterday date always) values to sql query and refresh the data on that basis all calculation for This year last year had been done in sql query itself. Based on this load i got differences as well.

       

      but now user need timeline slicer to choose enddate , now all the calculations depend on the end date variable

      selection. I tried to create the parameter and able to pass the parameter but in power bi pro user need to have knowledge where to pass parameter and than refresh , which is hard to teach to all user. They requested timelineslicer on which they can change the value and get the updated data. 

       

      What should i do to achieve this scenario, I tried to load the whole data from table but now confuse how to define This  year last year and difference 

       

       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

      Locationid

       

      4/1/2019

      10

      2

      0.5

      200

      1

       

      4/2/2019

      8

      0

      0.7

      201

      1

       

      4/3/2019

      7

      1

      1

      202

      1

       

      4/4/2019

      5

      0

      0.5

      203

      1

       

      4/5/2019

      1

      2

      0.9

      204

      1

       

      4/1/2019

      9

      4

      0.8

      205

      2

       

      4/2/2019

      2

      3

      0.8

      206

      2

       

      4/3/2019

      8

      0

      0.8

      207

      2

       

      4/4/2019

      4

      1

      0.8

      208

      2

       

      4/5/2019

      3

      1

      0.8

      209

      2

       

      4/1/2018

      10

      2

      0.8

      210

      1

       

      4/2/2018

      15

      5

      0.8

      211

      1

       

      4/3/2018

      7

      1

      0.8

      212

      1

       

      4/4/2018

      6

      0

      0.8

      213

      1

       

      4/5/2018

      3

      1

      0.8

      214

      1

       

      4/1/2018

      1

      2

      0.8

      215

      2

       

      4/2/2018

      12

      7

      0.8

      216

      2

       

      4/3/2018

      3

      2

      0.8

      217

      2

       

      4/4/2018

      4

      0

      0.8

      218

      2

       

      4/5/2018

      2

      2

      0.8

      219

      2

       

      Report contain 2 slicer Timelineslicer and LocationID Slicer

      		      

      startdate will always be first of that month 

      		      

      FT column,writeoff column value will be always the value based on Enddate

      		      

      SalesIn,salesoff, writeoff column will be caluculated sum between  startdate and endate 

      		      

      PercentageCalculatedColumn =sum salein between startdate and enddate / writeoff basedon timelineslicer end date value

      		      

      EndDate for Timeline Slicer

      LocationID Slicer

      4/4/2019

      ALL

      		      

       

      		      

      Matrix Result Expectation

       

       

       

      Actual

      LY

      Difference

       

      salesIn

      53

      58

      -5

       

      salesoff

      11

      19

      -8

       

      Wrieoff

      1.3

      3.2

      1.9

       

      Percenatge

      40.76923

      18.125

      22.644231

       

      FT

      411

      431

      -20

       

      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

      		    

      Thanks in advance!

      		      

       

      Thanks,

      Neha

      • MFelix's avatar
        MFelix
        Super User
        7 years ago

        Hi NehaSha ,

         

        Some question about the way you want to setup:

        • Wrieoff you say is o End Date however on PY calculation it seems to me it's based on Start date to End date can you plese check it?
        • Do you need the percentage on the same matrix?

        Based on the data you send you need to:

        • Unpivot all your columns except Location and Start date
        • Create the following measures:
        Current Year =
VAR selected_attribute =
    SELECTEDVALUE ( Sales[Attribute] )
RETURN
    IF (
        selected_attribute IN { "salesIn"; "salesoff" };
        CALCULATE (
            SUM ( Sales[Value] );
            FILTER (
                ALL ( Sales[startdate] );
                Sales[startdate] <= MAX ( Sales[startdate] )
                    && Sales[startdate]
                        >= DATE ( YEAR ( MAX ( Sales[startdate] ) ); MONTH ( MAX ( Sales[startdate] ) ); 1 )
            )
        );
        CALCULATE ( SUM ( Sales[Value] ) )
    )


Pryor Year =
CALCULATE ( Sales[Current Year]; DATEADD ( Sales[startdate]; -1; YEAR ) )

Difference = Sales[Current Year] - Sales[Pryor Year]

Percentage =
CALCULATE ( [Current Year]; Sales[Attribute] = "salesIn" )
    / CALCULATE ( [Current Year]; Sales[Attribute] = "Wrieoff" )

Percentage PY =
CALCULATE ( [Pryor Year]; Sales[Attribute] = "salesIn" )
    / CALCULATE ( [Pryor Year]; Sales[Attribute] = "Wrieoff" )

Percentage difference = [Percentage]-[Percentage PY]

        Now add the following data to your matrix:

        • Rows: attribute
        • Values: Measures - Current Year, Pryor Year, Difference

        Createa another matrix only with the percentage measures without the rows.

         

        Check PBIX file attach.

         

        Regards,

        MFelix