Forum Discussion

NehaSha's avatar
NehaSha
Icon for Helper II rankHelper 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     NetRentTY GrossPot Auction Percentage TotalArea Actual  $500,000 $510,550 39% 1000 LY $500,000 $510,550 40% 900...
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

NehaSha's avatar
NehaSha
Icon for Helper II rankHelper 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
Icon for Super User rankSuper 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
    Icon for Helper II rankHelper II
    7 years ago

    Thank you very much !It worked !

    :smileyhappy:

     

    Thanks Neha