Dynamic Ranking

[Kayla2020]

I'm trying to recreate in Power BI a weekly project ranking matrix that mimics the Excel table below. The goal is to show project data ranked by amount per calendar week with each matrix cell displaying: Amount, Project name, Region, Date

Desired Structure:

Rows: Ranking  (disconnected table)

Columns: Calendar timeline, including Week Start, Week End, and Week Number

Values: A formatted text string that combines amount, project name, region, and date

Dynamic Filtering: Rankings and results should respond to slicers like Region or PM

What I’ve done so far:

1. Created a calculated column for weekly ranking in the fact table:
Project_rank =
VAR cdate = Table1[Date]
VAR cweek = WEEKNUM(cdate, 21)
VAR cyear = Table1[Year]
VAR camount = Table1[Actual_Amt]
RETURN
IF (
NOT ISBLANK(cdate),
RANKX(
FILTER( ALLSELECTED(Table1),WEEKNUM(Table1[Date], 21)
= cweek &&YEAR(Table1[Date]) = cyear
), Table1[Actual_Amt] + (1 - 1 / DATEVALUE(Table1[Date])), ,
DESC,
DENSE),BLANK()
)

2. Created a measure to format the matrix value cells:
ProjectInfoFormatted =
VAR LineBreak = UNICHAR(10)
VAR EuroAmt = FORMAT(SUM(Table1[EuroAmount]), "#,##0")
VAR Project = SELECTEDVALUE(Table1[ProjectName])
VAR Region = SELECTEDVALUE(Table1[Region])
VAR IssueDate = FORMAT(SELECTEDVALUE(Table1[Date]), "dd/MM/yyyy")
RETURN
EuroAmt & LineBreak &
Project & LineBreak &
Region & LineBreak &
IssueDate & LineBreak

3. Created a disconnected table of rankings (1 to 50) and mapped it to the fact table with a many-to-one relationship.

The Issues:
i. The visual is not slicer dynamic i.e when I filter by Region or PM, the ranks don’t update correctly.  I get rows with 0 ranked first in for some weeks.

ii. Projects with the same amount in the same week are grouped into one matrix cell. I want each project to appear in its own row under the same rank, rather than being combined into one cell (tried to use the project name as a tie-breaker).

I am looking for a slicer-responsive solution where ranking updates correctly based on filters.

A way to ensure each project gets its own matrix row even when the amount is the same as another project in the same week.

Any ideas on what I'm doing wrong here?

[V-yubandi-msft]

Hi @Kayla2020 ,

As mentioned earlier, we had requested a sample to better understand and address your query accurately. Please share it here, and we’ll be happy to assist you further.

Thank you.

[V-yubandi-msft · Accepted solution]

Hi @Kayla2020 ,

Thanks for reaching out on the Fabric Community. Would you mind sharing a sample of your dataset. That would really help us better understand the issue you're facing sometimes it's easier to troubleshoot with actual data rather than just an image reference.

I hope you understand.

[Anonymous]

Hi @Kayla2020,
We noticed there hasn’t been any recent activity on this thread. As @V-yubandi-msft has already asked for sample data, can you please provide us some data to work on so that we can try and give you an accurate solution.

Best Regards,

Hammad.

[pankajnamekar25]

Hello @Kayla2020 

Replace This 

VAR Project = SELECTEDVALUE(tbl[ProjectName])

With this

VAR ProjectList =

    CONCATENATEX(

        FILTER(

            ALL(tbl),

            tbl[WeekNum] = SelectedWeek &&

            tbl[Actual_Amt] = SelectedAmt &&

            tbl[Region] = SelectedRegion &&

            tbl[PM] = SelectedPM

        ),

        tbl[ProjectName],

        ", "

    )

Thanks

 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...