I have a list of items with pricing as value by delivery date. Because the direct query data has multiple duplicated entries per items, the row subtotal shows 10X more than what it should be. If I create a new measure with rank to bring most recent data shown to make the subtotal, what should it be? Thank you in advance. This is what I thought to use rankx Rank =RANKX(ALL(Hub,Item),Item Cost)

sunah132 you can try this Measure = var TBL=SUMMARIZE('Sheet1',Sheet1[Date],Sheet1[Item],Sheet1[Item code],"min",min(Sheet1[Item Cost])+min(Sheet1[Misc Cost]))RETURN SUMX(TBL,[min])

sunah132 ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. You can get last price for an item like Measure = VAR __id = MAX ('Table'[item] )VAR __date = CALCULATE ( MAX('Table'[Date] ), ALLSELECTED ('Table' ), 'Table'[item] = __id ) CALCULATE ( max ('Table'[price] ), VALUES ('Table'[item] ),'Table'[item] = __id,'Table'[Date] = __date )

Thank you, I have attached a new example about this. https://www.dropbox.com/s/cc87c3wjzdra3bj/P1.pbix?dl=0 https://app.powerbi.com/links/XlboCMDYjw?ctid=da67ef1b-ca59-4db2-9a8c-aa8d94617a16&pbi_source=linkShare

sunah132 you can try this Measure 2 = var TBL=SUMMARIZE('Sheet1',Sheet1[Date],Sheet1[Item],Sheet1[Item code],"min",min(Sheet1[Item Cost])) RETURN SUMX(TBL,[min]) please see the attachment below

Thank you!! It applied as I was expected! If there's a miscellaneous cost on every Wednesday per item code, may I add like this?https://app.powerbi.com/links/XlboCMDYjw?ctid=da67ef1b-ca59-4db2-9a8c-aa8d94617a16&pbi_source=linkShare var TBL=SUMMARIZE('Sheet1',Sheet1[Date],Sheet1[Item],Sheet1[Item code],"min",min(Sheet1[Item Cost],(sheet1[Misc cost])) RETURN SUMX(TBL,[min])

Rank and Partition | Microsoft Fabric Community

11 Replies

amitchandak
Super User
5 years ago
sunah132 ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

You can get last price for an item like

Measure =
VAR __id = MAX ('Table'[item] )
VAR __date = CALCULATE ( MAX('Table'[Date] ), ALLSELECTED ('Table' ), 'Table'[item] = __id )
CALCULATE ( max ('Table'[price] ), VALUES ('Table'[item] ),'Table'[item] = __id,'Table'[Date] = __date )
- sunah132
  Helper I
  5 years ago
  Thank you, I have attached a new example about this.
  https://www.dropbox.com/s/cc87c3wjzdra3bj/P1.pbix?dl=0
  https://app.powerbi.com/links/XlboCMDYjw?ctid=da67ef1b-ca59-4db2-9a8c-aa8d94617a16&pbi_source=linkShare
  - ryan_mayu
    Super User
    5 years ago
    sunah132
    
    you can try this
    
    Measure 2 = var TBL=SUMMARIZE('Sheet1',Sheet1[Date],Sheet1[Item],Sheet1[Item code],"min",min(Sheet1[Item Cost])) RETURN SUMX(TBL,[min])
    
    please see the attachment below
    P1.pbix40 KB
Anonymous
Not applicable
5 years ago
Hi sunah132

I took a look at the pbix file you provided, and the current rendering is as follows. What is the result you need, could you explain it? From the data you provide so far, I am not particularly clear about your needs.

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- sunah132
  Helper I
  5 years ago
  Hi Ailsa,
  There are multiple duplicated data per item and I had to bring the minimum cost of the table per day to sum up the total. There were daily pricing and misc. pricing on every Wednesday that I had to put together into the table with minimums on it. I tried tweaking Ryan's DAX to both charges added onto it but I was struggling getting them calculated. Thank you for look into this!

Forum Discussion

Rank and Partition

11 Replies

Related content

RANK with PARTITION BY Like SQL

Duplicate ranking

partitioning concept

Partitioning Strategy

Ranking KPI card