Count invoice ID in category based on highest invoice line associated with invoice ID

[PA20]

Dear all. 

I am hoping for your assistance. I have a dataset with invoice data and I would like to only count the InvoiceID in the categories where the spend is the highest. 

Thanks in advance

[Anonymous · Accepted solution]

Hi @PA20 ,

Please try to create a new column with below dax formula:

Column =
VAR cur_id = [InvoiceID]
VAR cur_spend = [Spend]
VAR tmp =
    FILTER ( 'Table', [InvoiceID] = cur_id )
VAR max_speed =
    MAXX ( tmp, [Spend] )
VAR tmp1 =
    FILTER ( tmp, [Spend] = max_speed )
VAR ctn =
    COUNTROWS ( tmp1 )
RETURN
    IF ( cur_spend = max_speed, ctn, BLANK () )

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PA20]

Thank you for your swift reply @amitchandak . Spend is not a meassure, perhaps amount would have been a better naming option. The only use I have for the amount in this instance is as a sorting mechanism for my count by category - if that makes sense.

All the best.

[amitchandak]

@PA20 , I think you want to display those use TOPN with 1 of Index

Assuming spend is a measure

calculate([Spend], keepfilters(index(1, allselected(Table[InvoiceID], Table[Category]), orderBy([Spend], desc) ,partitionBy(Table[Category]))) )

Power BI Index function: Top/Bottom Performer by name and value- https://youtu.be/HPhzzCwe10U

TOPN: https://youtu.be/QIVEFp-QiOk
TOPN with Numeric Parameter -https://youtu.be/cN8AO3_vmlY?t=26448