Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |