Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Guys,
My mind has gone completely blank on this so perhaps somebody can point me in the right direction.
I've got a table which contains asset records, In it's simplest form it look like this below:
I've been asked to find out
1. the oldest 25% of assets in the estate (the age is based on the purchase date column)
2. The cost of replacing the oldest 25% (a replacement laptop = £1300, a replacement Desktop = £750, a replacement server = £5000)
I just can't figure out the best function to use to create the necessary measures. Any ideas very welcome 🙂
Hi @Anonymous ,
You could use RANKX() function to get the oldest 25% records.
Formulas for your reference.
measure 1 = SELECTEDVALUE('Table'[date])
Measure 2 =
RANKX ( ALL ( 'Table' ), [measure 1],, ASC, DENSE )
Measure 3 =
IF ( [Measure 2] < MAXX ( ALL ( 'Table' ), [Measure 2] ) * .25, 1, 0 )
Measure 4 =
IF (
[Measure 3] = 1,
SWITCH (
TRUE (),
SELECTEDVALUE ( 'Table'[type] ) = "laptop", 1500,
SELECTEDVALUE ( 'Table'[type] ) = "desktop", 750,
SELECTEDVALUE ( 'Table'[type] ) = "server", 5000
),
SELECTEDVALUE ( 'Table'[value] )
)
Measure 5 = SUMX(ALL('Table'),'measure'[Measure 4])
Measure 1 and 2 do the rank work, measure 3 marks the oldest 25% records then measure 4 replace the values and measure 5 sum the values.
In additon, you could combine these formulas if you want.
Best Regards,
Jay
Thanks for your help Jay. This is looking more promising.
Measure 1 works fine. There's an issue when I add Measure 2 to the table though. Measure 1 returns blank for some assets and measure 2 returns "1" for a lot of them (because measure 1 cant return the purchase date)....
@Anonymous
To calculate the replacement cost of the oldest 25% of the assets, add a new column to your assets table to calculate the age in days as follows:
Asset Age = DATEDIFF( [Purchase Date] , TODAY() , DAY )
Create the following Measure to calculate the Replacement Cost: If you need to change the 25%, change the _Age value
Replacement Cost-Oldest 25% =
VAR _Age = 0.25
VAR _TOTAL = COUNTROWS( ALLSELECTED(Table4) )
RETURN
SUMX(
Table4,
VAR _BELOW = COUNTROWS( FILTER( ALL(Table4[Asset Age]) , Table4[Asset Age] < EARLIER([Asset Age]) )) RETURN
IF( DIVIDE( _BELOW , _TOTAL ) >= (1-_Age) ,
SWITCH( Table4[Machine Type] ,
"Laptop" , 1300,
"Desktop" , 750,
"Server" , 5000
)
)
)
Desired output:
________________________
If my answer was helpful, please Accept it as the solution to help the other members
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thanks. I tried this but don't seem to get any values using the new measure? Not sure what I'm doing wrong!
@PhoenixBird
Can you check if any date filter is applied? Hope you can see the Asset Age Column with values.
Share a sample PBIX file with dummy data to check
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Anonymous , I think you need to create Rank and find percentile. Based on the purchase date.
Add random Seconds to date an try to create a Unique Rank column on the purchase date and then divide by max rank to get below .25
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale // Tie breaker for Rank
Top Percentile
https://blog.enterprisedna.co/implementing-80-20-logic-in-your-power-bi-analysis/
https://forum.enterprisedna.co/t/testing-the-pareto-principle-80-20-rule-in-power-bi-w-dax/459
Hi @Anonymous
You could try this
Go Query Editor, sort the date from oldest to newest, add an index column. Then, create a new measure with the MAX function on the index column and divide it by 4 (which gives you 25%). I think the measure will look like this: Old_25 = MAX([IndexColumn]/4
Then apply a filter on the index column, any number that is smaller or equal to Old_25 will be your oldest 25% asset.
Thanks for the suggestion,. I added an index column and tried to do the measure as you described but it doesnt quite work:
Hi @Anonymous
Create a new column in the table with all your data:
Old 25% Filter = if([Index] <= [Oldest 25%], "Yes", "No")
Anything here that's a "Yes" will be your oldest 25% asset.
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |