Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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!
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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
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.
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
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
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hey man. When I place it into the Matrix like in the pic below, no matrix appears, I just get a spinning wheel.
@Anonymous
Drop Matter key in a table and this measure.
I will check the matrix
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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!
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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.
@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
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
User | Count |
---|---|
102 | |
84 | |
77 | |
70 | |
67 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |