cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shareezsaleem
Helper II
Helper II

Get the latest & most repeated Unit Prices

Hi All, I am looking to extract latest value and most repeated value (if tie get max).

 

Item UOM Unit Price Order Date
Item1 EA 95.00 25-Jan-22
Item1 Carton 1,800.00 24-Feb-22
Item1 EA 95.00 19-Feb-22
Item1 EA 80.00 1-Mar-22

 

Result should be like this:

Item UOM Latest Unit Price Most Repeated
Item1 EA 80 95

 

Looking for dax meaure not calculated column measure.

1 ACCEPTED SOLUTION
ddpl
Solution Sage
Solution Sage

@shareezsaleem try by creating below Measures

UOM  = var T1 = SUMMARIZE('Table','Table'[UOM],"Count",COUNTROWS('Table'))
       var T2 = MAXX(T1,[Count])
     return
       MAXX( T1, IF([Count] = T2,'Table'[UOM])) 
 
Latest Unit Price = var _M = MAXX('Table','Table'[Order Date])
                       return
                       CALCULATE(MAX('Table'[Unit Price]),'Table'[Order Date] = _M)
 
Most Repeated = var T1 = SUMMARIZE('Table','Table'[Unit Price],"Count",COUNTROWS('Table'))
            var T2 = MAXX(T1,[Count])
             return
MAXX( T1, IF([Count] = T2,'Table'[Unit Price]))
 

View solution in original post

1 REPLY 1
ddpl
Solution Sage
Solution Sage

@shareezsaleem try by creating below Measures

UOM  = var T1 = SUMMARIZE('Table','Table'[UOM],"Count",COUNTROWS('Table'))
       var T2 = MAXX(T1,[Count])
     return
       MAXX( T1, IF([Count] = T2,'Table'[UOM])) 
 
Latest Unit Price = var _M = MAXX('Table','Table'[Order Date])
                       return
                       CALCULATE(MAX('Table'[Unit Price]),'Table'[Order Date] = _M)
 
Most Repeated = var T1 = SUMMARIZE('Table','Table'[Unit Price],"Count",COUNTROWS('Table'))
            var T2 = MAXX(T1,[Count])
             return
MAXX( T1, IF([Count] = T2,'Table'[Unit Price]))
 

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors