Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

DAX measure needed to Find the oldest 25% of records

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:

 

Capture.JPG

 

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 🙂

9 REPLIES 9
v-jayw-msft
Community Support
Community Support

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.

2.PNG

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Anonymous
Not applicable

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)....

 

PhoenixBird_2-1600081515347.png

 

 

 

 

Fowmy
Super User
Super User

@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 )

 

 

 

Fowmy_0-1599723808079.png

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:

Fowmy_1-1599723976202.png

________________________

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 🙂

YouTube  LinkedIn

 




 

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

Thanks. I tried this but don't seem to get any values using the new measure? Not sure what I'm doing wrong!

 

PhoenixBird_0-1599732498125.png

 

@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 🙂

YouTube Linkedin

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

amitchandak
Super User
Super User

@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

darentengmfs
Post Prodigy
Post Prodigy

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.

Anonymous
Not applicable

Thanks for the suggestion,. I added an index column and tried to do the measure as you described but it doesnt quite work:

 

PhoenixBird_0-1599683130041.png

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.