Forum Discussion
Blank Row in Matrix
Hi,
I have a matrix which lists all of our partnerships, and their revenue generated. I also have a DAX formula which ranks them based on their revenue. The matrix uses the JV key which is on a dimension table which is linked to my large data set. The large data set includes some parternships which are no longer with us, so when I add the JV key to the matrix with initial revenue, it spits out a blank row. This is fine, I can just filter out blank rows on the matrix.
My issue is with the rank formula, because it includes past parternships, their revenue is quite a lot and they are ranked 3rd when all summed together. When I filter blank JV keys out the table, the rank DAX still includes it as if they are there. It goes 1,2,4,5,6 etc. I want it so it doesnt include the blank JVs in the ranking DAX.
I have attached pics of my matrix with and without the filter on. This is my rank formula:
2 Replies
samblackshaw28 , You can try allselected
RANKX( ALselectedL(JV_Lookup[JV Key]),
CALCULATE(
SUM(JV_Revenue_Summary[Revenue 2]),
JV_Revenue_Summary[Revenue Type]="Initial"))
Also, If possible, you can use Visual calculations for Rank or Rownumberhttps://medium.com/microsoft-power-bi/next-level-power-bi-visual-calculations-with-offset-index-rank-rownumber-and-rankx-20618b5989c0
Power BI Visual Calculations with Offset, Index, Rank, RowNumber| February 2024 Update:
https://www.youtube.com/watch?v=JwMF0endTPY&list=PLPaNVDMhUXGYo50Ajmr4SgSV9HIQLxc8LHi,
Share some data to work with and show the expected result. Share data in a format that can be pasted in an MS Excel file.
