Forum Discussion
Helper IIMatrix Table Transpose Rows represent column and Column represent Rows with multiple data type $,%
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
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
Helper IIHi MFelix ,
Thanks for sharing both ideas!
Please see the below mentioned scenarios:
- 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
Super User
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 !
:smileyhappy:
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