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

[NehaSha]

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

to 

	Actual	LY
NetRent	$500,000	$500,000
GrossPot	$510,550	$510,550
Auction Percentage	39%	40%
TotalArea	1000	900

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

Thanks in advance!

Thanks,

Neha

[MFelix · Accepted solution]

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 · Accepted solution]

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

[NehaSha]

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

[NehaSha]

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]

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

[NehaSha]

Hi @MFelix ,

Thank you very much for reply!

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?---Yes all calculation for all columns depend on User selected End date for this year and last year 
• Do you need the percentage on the same matrix?---Yes I need to show all the attribute in one table
• Is There a possiblity for datatypes with currency,comma,Percentage sign,someplace dont need more thn 1 decimal place, someplace no decimal is required to show ,as suggested solution, right now all data is in decimal values:

• Matrix Result Expectation
  	Actual	LY	Difference
  salesIn	$53	$58	($5)
  salesoff	$11	$19	($8)
  Wrieoff	1.3	3.2	1.9
  Percenatge	40.8%	44.6%	3.9%	Percentage some columns with 1 decimal place some without decimal place
  FT	411	431	-20	if number is greater than thousands than comma

Appreciate your help!

Thanks,

Neha

[MFelix · Accepted solution]

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]

Thank you very much !It worked !

Thanks Neha

[MFelix · Accepted solution]

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

[nardtmo]

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]

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

NetRentTY	GrossPot	Auction Percentage	TotalArea	LocationID
$500,000	$510,550	39%	1000	1

Query2 Represent 

NetRentLY	GrossPotLY	Auction PercentageLY	TotalAreaLY	LocationID
$410,550	$413,000	20%	900.08	1

Need Result:

	Actual	LY
NetRent	$500,000	$410,550
GrossPot	$510,550	$413000
Auction Percentage	39%	20%
TotalArea	1,000	900.08

Please help with this real scenario !

Thanks,

Neha

[MFelix · Accepted solution]

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

[NehaSha]

Hi @MFelix ,

Need your help for the below mentioned scenario !

I need to show difference column next to  LY Column, I can  easily add subtotal  but how to get the difference Actual-LY 

	Actual	LY	Difference
NetRentTY	$500,000	$410,550	$89,450
GrossPot	$510,550	$430,000	$80,550
Auction Percentage	39%	40%	-1%
TotalArea	1000	900.08	99.92

Thanks,

Neha

[MFelix]

Hi @NehaSha ,

Can you please confirm how you data looks like currenctly? You have one line for actuals and another for PY?

Regards,

MFelix

[NehaSha]

Hi  @MFelix 

Please see the below mentioned :

Query1 

NetRentTY	GrossPot	Auction Percentage	TotalArea	LocationID
$500,000	$510,550	39%	1000	1

Query2 

NetRentLY	GrossPotLY	Auction PercentageLY	TotalAreaLY	LocationID
$410,550	$413,000	20%	900.08	1

After found the solution provided by you, able to achieve the below mentioned Expected Result, 

Currently data look like below mentioned:

	Actual	LY
NetRent	$500,000	$410,550
GrossPot	$510,550	$413000
Auction Percentage	39%	20%
TotalArea	1,000	900.08

Need to add one more column for Difference:

	Actual	LY	Difference
NetRentTY	$500,000	$410,550	$89,450
GrossPot	$510,550	$430,000	$80,550
Auction Percentage	39%	40%	-1%
TotalArea	1000	900.08	99.92

Thanks,

Neha

[MFelix]

Hi @NehaSha ,

One option is to do some changes on your power query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjUAASUdJVNDA1NTEMPYUhVIGkJEDYHYMbmkNDFHKVYnWskEpsjE0BiiwMgApNrSwEDPwAKq3idSKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [NetRentTY = _t, GrossPot = _t, #"Auction Percentage" = _t, TotalArea = _t, LocationID = _t, Type = _t]),
    Format = Table.TransformColumnTypes(Source,{{"NetRentTY", Int64.Type}, {"GrossPot", Int64.Type}, {"Auction Percentage", Percentage.Type}, {"TotalArea", type number}, {"LocationID", Int64.Type}}),
    Filter_Actuals = Table.SelectRows(Format, each ([Type] = "Actual")),
    UnpivotActuas = Table.UnpivotOtherColumns(Filter_Actuals, {"Type"}, "Attribute", "Value"),
    Custom1 = Format,
    Filter_LY = Table.SelectRows(Custom1, each ([Type] = "LY")),
    Unpivote_LY = Table.UnpivotOtherColumns(Filter_LY, {"Type"}, "Attribute", "Value"),
    #"Merged Queries" = Table.NestedJoin(UnpivotActuas,{"Attribute"},Unpivote_LY,{"Attribute"},"Unpivote_LY",JoinKind.LeftOuter),
    #"Expanded Unpivote_LY1" = Table.ExpandTableColumn(#"Merged Queries", "Unpivote_LY", {"Value"}, {"Unpivote_LY.Value"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Unpivote_LY1", "Difference", each if [Attribute] = "LocationID" then [Value] else [Value]-[Unpivote_LY.Value]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Value", "Unpivote_LY.Value", "Type"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Difference"),
    #"Appended Query" = Table.Combine({Source, #"Pivoted Column"}),
    #"Replaced Value" = Table.ReplaceValue(#"Appended Query",null,"Var Difference",Replacer.ReplaceValue,{"Type"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"NetRentTY", Int64.Type}, {"GrossPot", Int64.Type}, {"Auction Percentage", Percentage.Type}, {"TotalArea", type number}, {"LocationID", Int64.Type}})
in
    #"Changed Type"

You need to do some pivots and unpivots and then a append of the values.

On DAX you can use the creation of a new table with the formula below:

Table =
VAR NetRent =
    CALCULATE (
        SUM ( Table1[NetRentTY] );
        FILTER ( ALL ( Table1[Type] ); Table1[Type] = "Actual" )
    )
        - CALCULATE (
            SUM ( Table1[NetRentTY] );
            FILTER ( ALL ( Table1[Type] ); Table1[Type] = "LY" )
        )
VAR Grosspot =
    CALCULATE (
        SUM ( Table1[GrossPot] );
        FILTER ( ALL ( Table1[Type] ); Table1[Type] = "Actual" )
    )
        - CALCULATE (
            SUM ( Table1[GrossPot] );
            FILTER ( ALL ( Table1[Type] ); Table1[Type] = "LY" )
        )
VAR AuctionPercentage =
    CALCULATE (
        SUM ( Table1[Auction Percentage] );
        FILTER ( ALL ( Table1[Type] ); Table1[Type] = "Actual" )
    )
        - CALCULATE (
            SUM ( Table1[Auction Percentage] );
            FILTER ( ALL ( Table1[Type] ); Table1[Type] = "LY" )
        )
VAR TotalArea =
    CALCULATE (
        SUM ( Table1[TotalArea] );
        FILTER ( ALL ( Table1[Type] ); Table1[Type] = "Actual" )
    )
        - CALCULATE (
            SUM ( Table1[TotalArea] );
            FILTER ( ALL ( Table1[Type] ); Table1[Type] = "LY" )
        )
VAR Location =
    MAX ( Table1[LocationID] )
RETURN
    UNION (
        Table1;
        ROW (
            "NetRentTy"; NetRent;
            "GrossPot"; Grosspot;
            "Auction Percentage"; AuctionPercentage;
            "TotalArea"; TotalArea;
            "LocationID"; Location;
            "Type"; "Difference"
        )
    )

Check the result in the file attach. (in this the line difference is repeated since I use the result from the query table

Regards,

MFelix

[NehaSha]

Thank You very much! it worked !