Forum Discussion

georgec96's avatar
georgec96
Helper II
4 years ago
Solved

Create a "priority list"

Hi All,

 

I'm having a bit of problem trying to find the right formula for my data problem.

 

I have 3 columns PO Number, PO Age, Backorders, Overdues.

 

I would like to create some sort of priority list that would look like this : the oldest PO that can clear the most Overdues and the most Backorders. Column overdues should have the highest priority.

 

What I was thinking is to give each column some sort of "weight"(column overdues having the biggest weight) and sort by that but im not entirely sure how to put that into a formula.

 

I've also tried to sort my dataset by multiple columns (Overdues,Backorders,Age) but I believe that is not solving my problem as it will put the oldest PO at the bottom of the dataset.

 

Example dataset

 

  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

    It is for creating a new column.

    The concept is to have ranking number for each column, and then give a weight to the each ranking.

    I tried to create a sample pbix file like below, and I hope the below solution can provide you the idea on creating a solution for your data model.

     

     

    Priority CC =
VAR _addprioritycolumn =
    ADDCOLUMNS (
        Data,
        "@priority",
            RANKX ( Data, Data[Overdues],, ASC ) * 100000000
                + RANKX ( Data, Data[Backorders],, ASC ) * 10000
                + RANKX ( Data, Data[PO Age in days],, ASC )
    )
VAR _addrankcolumn =
    ADDCOLUMNS (
        _addprioritycolumn,
        "@rank", RANKX ( _addprioritycolumn, [@priority],, DESC )
    )
RETURN
    MAXX (
        FILTER ( _addrankcolumn, Data[PO Number] = EARLIER ( Data[PO Number] ) ),
        [@rank]
    )

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

    It is for creating a new column.

    The concept is to have ranking number for each column, and then give a weight to the each ranking.

    I tried to create a sample pbix file like below, and I hope the below solution can provide you the idea on creating a solution for your data model.

     

     

    Priority CC =
VAR _addprioritycolumn =
    ADDCOLUMNS (
        Data,
        "@priority",
            RANKX ( Data, Data[Overdues],, ASC ) * 100000000
                + RANKX ( Data, Data[Backorders],, ASC ) * 10000
                + RANKX ( Data, Data[PO Age in days],, ASC )
    )
VAR _addrankcolumn =
    ADDCOLUMNS (
        _addprioritycolumn,
        "@rank", RANKX ( _addprioritycolumn, [@priority],, DESC )
    )
RETURN
    MAXX (
        FILTER ( _addrankcolumn, Data[PO Number] = EARLIER ( Data[PO Number] ) ),
        [@rank]
    )