Rank and Partition

[sunah132]

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)

[v-yetao1-msft]

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]

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!

[amitchandak]

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

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=linkSh... 

[ryan_mayu]

@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

[sunah132]

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=linkSh... 

var TBL=SUMMARIZE('Sheet1',Sheet1[Date],Sheet1[Item],Sheet1[Item code],"min",min(Sheet1[Item Cost],(sheet1[Misc cost]))
RETURN SUMX(TBL,[min])

[ryan_mayu]

@sunah132 

I can't access to the link you shared. could you pls share via dropbox?

[sunah132]

Yes, Sorry. Here is the dropbox link below.

https://www.dropbox.com/s/2ead8gbkb7tzrlx/P1.pbix?dl=0 

[ryan_mayu]

@sunah132 

I can download your pbix file. what's the expected output?

[sunah132]

Hello,

As the table below, I tried to see if I can add the MIN of Misc charge add up with the item cost if they were on the same delivery date. Thank you very much!

[ryan_mayu]

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