Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
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 | |||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||
Thanks in advance! |
Solved! Go to Solution.
Hi @NehaSha
You may use Format Function to get the table.Please refer to this article.Then add a measure and use it in conditional formatting to get red colors.For percentage and comma,you may use format under modeling.There's no better way to show them in same part of table as there's no percentage in attribute column.Attached sample file for your reference.
Actual = IF ( MAX ( Table1[Attribute] ) IN { "salesIn", "salesoff" }, FORMAT ( CALCULATE ( SUM ( Table1[Value] ), FILTER ( ALL ( Table1[startdate] ), Table1[startdate] <= MAX ( Table1[startdate] ) && Table1[startdate] >= DATE ( YEAR ( MAX ( Table1[startdate] ) ), MONTH ( MAX ( Table1[startdate] ) ), 1 ) ) ), "$#,##0;($#,##0)" ), CALCULATE ( SUM ( Table1[Value] ) ) )
Measure = IF([Actual]-[LY]<0,1)
Regards,
Hi @NehaSha
You may unpivot the table in query editor first and then create measures like below:
Actual = IF ( MAX ( Table1[Attribute] ) IN { "salesIn", "salesoff" }, CALCULATE ( SUM ( Table1[Value] ), FILTER ( ALL ( Table1[startdate] ), Table1[startdate] <= MAX ( Table1[startdate] ) && Table1[startdate] >= DATE ( YEAR ( MAX ( Table1[startdate] ) ), MONTH ( MAX ( Table1[startdate] ) ), 1 ) ) ), CALCULATE ( SUM ( Table1[Value] ) ) )
LY = CALCULATE([Actual],SAMEPERIODLASTYEAR(Table1[startdate]))
Difference = [Actual]-[LY]
Percentage Actual = DIVIDE ( CALCULATE ( [Actual], Table1[Attribute] = "salesIn" ), CALCULATE ( [Actual], Table1[Attribute] = "wrieoff" ) )
Percentage LY = DIVIDE ( CALCULATE ( [LY], Table1[Attribute] = "salesIn" ), CALCULATE ( [Actual], Table1[Attribute] = "wrieoff" ) )
Percentage Difference = [Percentage Actual]-[Percentage LY]
Regards,
Hi @v-cherch-msft ,
Really appreciate for your reply!
It worked out as suggested but in requirement there are below mentioned things need to accomplish for user acceptance:
1. Data Type for each attribute such as $, number with comma, 1 decimal place. Not sure how it can be done.
2. Need Percentage Column in one table rather than another table, Is there any way out ?
3. Need to represent negative numbers in red.
Matrix Result Expectation | ||||
Actual | LY | Difference | ||
salesIn | $53 | $58 | ($5) | |
salesoff | $11 | $19 | ($8) | need to show negative numbers in red |
Wrieoff | 1 | 3 | 2 | without decimal |
Percenatge | 40.8% | 44.6% | 3.9% | Percentage some columns with 1 decimal place some --suppose to show in same part of table rather than different table |
FT | 411 | 431 | -20 | if number is in million than show with comma |
Thank you very much in advance!
Thanks,
Neha
Hi @NehaSha
You may use Format Function to get the table.Please refer to this article.Then add a measure and use it in conditional formatting to get red colors.For percentage and comma,you may use format under modeling.There's no better way to show them in same part of table as there's no percentage in attribute column.Attached sample file for your reference.
Actual = IF ( MAX ( Table1[Attribute] ) IN { "salesIn", "salesoff" }, FORMAT ( CALCULATE ( SUM ( Table1[Value] ), FILTER ( ALL ( Table1[startdate] ), Table1[startdate] <= MAX ( Table1[startdate] ) && Table1[startdate] >= DATE ( YEAR ( MAX ( Table1[startdate] ) ), MONTH ( MAX ( Table1[startdate] ) ), 1 ) ) ), "$#,##0;($#,##0)" ), CALCULATE ( SUM ( Table1[Value] ) ) )
Measure = IF([Actual]-[LY]<0,1)
Regards,
Hi @v-cherch-msft ,
Thank you very much for reply and give hope to the project!
I need your help with below mentioned scenario which I couldn't cover in the sample example and have these questions now:
Formating : Looks like in current scenario the calculation based on between startdate and enddate showing always $ sign and the values depend on enddate dont show $ sign if required :
Sorting: right now sorting based on attribute asc or desc order , is it possible to sort as user requested without any key point.
Percentage Column: In order to show the project as final presentation the biggest thing is how to get the Percentage column as a part of the same table. In sample it just showing one percentage column but in reality I have total 6 Percentage attribute: Please help to get the Percentage column as a part of same attribute
In Attached PBIX the result is
Attribute | Actual | LY | Difference |
NetRent | $1,632,000 | $1,712,000 | $80,000 |
salesoff | $11 | $19 | $8 |
salesIn | $53 | $58 | $5 |
Writeoff | 1 | 2 | 0 |
Ft | 411 | 431 | -20 |
Expectation :
Attribute | Actual | LY | Difference | ||
salesoff | 11 | 19 | 8 | no need $sign | calculation based on startdate and enddate |
salesIn | 53 | 58 | 5 | no need $sign | calculation based on startdate and enddate |
NetRent | $1,632,000 | $1,712,000 | $80,000 | need $sign | calculation based on startdate and enddate |
Writeoff | 1.3 | 3.2 | 1.9 | one decimalplace | calculation based on enddate |
PerOcc | 40.8% | 44.6% | -3.8% | need Percentage column with 1 decimal as part of table | calculation based on startdate and enddate |
Ft | $411 | $431 | ($20) | nodecimal place but $sign | calculation based on enddate |
sorting attribute in user requested order there is no asc or desc criteria |
Thanks,
Neha
Hi @v-cherch-msft ,
I would like to follow up , if you have time to look into the below mentioned scenario ? It will be really helpful!
Is it really possible to achieve?
Formating : Looks like in current scenario the calculation based on between startdate and enddate showing always $ sign and the values depend on enddate dont show $ sign if required :
But is it possible to define each attribute format, I have added new column netrent (Copy of FT column added 3zeros in end for each value) which also calculated based on startdate and enddate shows $ sign but salesoff and salesIn dont need $ sign. similarly writeoff need one decimal place and FT need $ sign without decimal place.
Sorting: right now sorting based on attribute asc or desc order , is it possible to sort as user requested without any key point. neither sorting based on Attribute alphabet nor on values , its just user requested in the below mentioned order.
Percentage Column: In order to show the project as final presentation the biggest thing is how to get the Percentage column as a part of the same table. In sample it just showing one percentage column but in reality I have total 6 Percentage attribute. Please help to get the Percentage column as a part of same attribute
In Attached PBIX the result is
Attribute | Actual | LY | Difference |
NetRent | $1,632,000 | $1,712,000 | $80,000 |
salesoff | $11 | $19 | $8 |
salesIn | $53 | $58 | $5 |
Writeoff | 1 | 2 | 0 |
Ft | 411 | 431 | -20 |
Expectation :
Attribute | Actual | LY | Difference | ||
salesoff | 11 | 19 | 8 | no need $sign | calculation based on startdate and enddate |
salesIn | 53 | 58 | 5 | no need $sign | calculation based on startdate and enddate |
NetRent | $1,632,000 | $1,712,000 | $80,000 | need $sign | calculation based on startdate and enddate |
Writeoff | 1.3 | 3.2 | 1.9 | one decimalplace | calculation based on enddate |
PerOcc | 40.8% | 44.6% | -3.8% | need Percentage column with 1 decimal as part of table | calculation based on startdate and enddate |
Ft | $411 | $431 | ($20) | nodecimal place but $sign | calculation based on enddate |
sorting attribute in user requested order there is no asc or desc criteria |
Thanks,
Neha
I tried but couldnt attach the PBIX. But the NetRent Column is the Duplicate column for FT and rename to 'NetRent' and added 3 zeros in the end for all the values, added in change type
= Table.DuplicateColumn(Source, "Ft", "Ft - Copy")
= Table.ReplaceValue(#"Duplicated Column","200","200000",Replacer.ReplaceText,{"Ft - Copy"})--repeat for all values
= Table.RenameColumns(#"Replaced Value19",{{"Ft - Copy", "NetRent"}})
= Table.TransformColumnTypes(#"Renamed Columns",{{"startdate", type date}, {"salesIn", Int64.Type}, {"salesoff", Int64.Type}, {"Wrieoff", type number}, {"Ft", Int64.Type}, {"Locationid", Int64.Type},{"NetRent",Int64.Type}})
Actual Measure change:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |