Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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]
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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]
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |