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 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
Icon for Super User rankSuper 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

  • NehaSha's avatar
    NehaSha
    Icon for Helper II rankHelper II
    7 years ago

    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   
       ActualLYDifference 
      salesIn$53$58($5) 
      salesoff$11$19($8) 
      Wrieoff1.33.21.9 
      Percenatge40.8%44.6%3.9%Percentage some columns with 1 decimal place some without decimal place 
      FT411431-20if number is greater than thousands than comma 

     

    Appreciate your help!

     

    Thanks,

    Neha