Ranking Top 3 Most Expensive Invoices of Top 3 most expensive Projects

[Anonymous]

Didn't find much on ranking top N for a category at a lower granularity. Here, I want to Rank Top 3 Most Expensive Invoices of Top 3 most expensive Projects/Matters.  So Invoices are at the lower granularity. I was able to write the formula for top 3 Matters, but I tried writing Top N of Top N and it came out absolutely wrong and I ended up deleting it.  Any ideas? Data is attached below in the Google Drive Link

https://drive.google.com/drive/folders/1Kcr3j1baRjdvUOIQb_rMt95eSaCS2Mvi?usp=sharing

[Fowmy · Accepted solution]

Hi, @Anonymous 

Please check this solution, hope this will answer the question.

Top 3 Invoices in Top 3 Matters = 
VAR TOP3MAT = 
    TOPN(3,
        SUMMARIZE(
            ALL(FactInvoiceHeader[Matter Key]),
            FactInvoiceHeader[Matter Key],
            "Amount",[Total Spend X]
        ),[Amount],DESC
    )
VAR TOP3INV = 
    TOPN(3,
        SUMMARIZE(
            ALL(FactInvoiceHeader[Invoice Header Key]),
            FactInvoiceHeader[Invoice Header Key],
            "Amount",[Total Spend X]
        ),[Amount],DESC
    )
RETURN
    CALCULATE(
        [Total Spend X],
        TOP3INV,
        TOP3MAT
    )

Thanks

[amitchandak]

@Anonymous ,

refer if this can help

https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
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
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

[Anonymous]

Amazing. Those were actually some of the reference material I used, was still unable to figure out a Top 3 most expensive invoices for top 3 most expensive projects and surprisingly haven't seen much material in the community on it.

[harshnathani]

Hi @Anonymous ,

You can try this

Rank 3 = 
IF (
    ISINSCOPE ( FactInvoiceHeader[Invoice Header Key] ),
    RANKX (
        CALCULATETABLE (
            VALUES ( FactInvoiceHeader ),
            ALLSELECTED ( FactInvoiceHeader[Invoice Header Key] )
        ),
       CALCULATE(sum(FactInvoiceHeader[Invoice Net Amount]))
    ),
    IF (
        ISINSCOPE ( FactInvoiceHeader[Matter Key] ),
        VAR CC = CALCULATE(SUM(FactInvoiceHeader[Invoice Net Amount] ) )
        RETURN
            CALCULATE (
                RANKX (
                    VALUES ( FactInvoiceHeader[Matter Key] ),
                    CALCULATE(SUM(FactInvoiceHeader[Invoice Net Amount])),
                    CC
                ),
                ALLSELECTED ()
            )
    )

Regards,

Harsh Nathani

[Fowmy · Accepted solution]

Hi, @Anonymous 

Please check this solution, hope this will answer the question.

Top 3 Invoices in Top 3 Matters = 
VAR TOP3MAT = 
    TOPN(3,
        SUMMARIZE(
            ALL(FactInvoiceHeader[Matter Key]),
            FactInvoiceHeader[Matter Key],
            "Amount",[Total Spend X]
        ),[Amount],DESC
    )
VAR TOP3INV = 
    TOPN(3,
        SUMMARIZE(
            ALL(FactInvoiceHeader[Invoice Header Key]),
            FactInvoiceHeader[Invoice Header Key],
            "Amount",[Total Spend X]
        ),[Amount],DESC
    )
RETURN
    CALCULATE(
        [Total Spend X],
        TOP3INV,
        TOP3MAT
    )

Thanks

[Anonymous]

Hey man. When I place it into the Matrix like in the pic below, no matrix appears, I just get a spinning wheel.

[Fowmy]

@Anonymous 

Drop Matter key in a table and this measure. 

I will check the matrix 

[Fowmy · Accepted solution]

@Anonymous 

If you need to see the Top 3 Matters and the related top 3 invoices based on the amount in  Matrix,

Use this measure:

Top 3 Invoices in Top 3 Matters = 
VAR TOP3MAT = 
    TOPN(3,
        SUMMARIZE(
            ALLSELECTED(FactInvoiceHeader[Matter Key]),
            FactInvoiceHeader[Matter Key],
            "Amount",[Total Spend X]
        ),[Amount],DESC
    )
VAR TOP3INV = 
    TOPN(3,
        SUMMARIZE(
            ALLSELECTED(FactInvoiceHeader[Invoice Header Key]),
            FactInvoiceHeader[Invoice Header Key],
            "Amount",[Total Spend X]
        ),[Amount],DESC
    )
RETURN
    CALCULATE(
        [Total Spend X],
        TOP3MAT,
        KEEPFILTERS(TOP3INV)
    )

 
Did I answer your question? Mark my post as a solution! 

[Anonymous]

@Fowmy  when I put this into a Matrix with Matter Key at the top of the hierarchy and Invoice Header Key under, I only got blanks......I'll post a picture.

[Fowmy]

@Anonymous 

You should get this:

If you are satisfied with my answer, please mark it as a solution so others can easily find it.

APPRECIATE KUDOS!

Subscribe to ExcelFort and learn Power BI, Power Query and Excel

[Anonymous]

@Fowmy  thanks man worked great.