• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

DAX formula for Average Rank Calculation in Power BI

R

Rameez Sarang

Member
I need to calculate Average USD $ Rank by user selection and year. So if user selects Sheldon and Jack the combined average for year Jan22 to Dec 22 is $520,100 which needs to be ranked with other names for this year. The rank is 7 when compared with other names in the data. I need a dax which calculates this correctly.
 

Attachments

  • Copy of data.xlsx
    13.5 KB · Views: 4

Rameez Sarang​

Have You ... just waited for ten days?
You should able to 'sell' ... make this interesting ... thread to get replies.
Is there expected results ... because You've written I need a dax which calculates this correctly.
 
This is result I expect - If the user selects Sheldon and Jack the combined average for year Jan22 to Dec 22 is $520,100 which needs to be ranked with other names for this year. The rank is 7 when compared with other names in the data.
 
@Rameez Sarang
Your issue is that you are asking DAX/Data model related question. Yet, your file only contains flat list. With no data model or schema.
DAX is highly contextual in it's nature and will evaluate differently based on context it's evaluated on.

Without knowing your data model structure, no one will be able to give you an answer. After all, we are not a mind reader ;)
 
Chihiro said:
@Rameez Sarang
Your issue is that you are asking DAX/Data model related question. Yet, your file only contains flat list. With no data model or schema.
DAX is highly contextual in it's nature and will evaluate differently based on context it's evaluated on.

Without knowing your data model structure, no one will be able to give you an answer. After all, we are not a mhave been trying to calculate the rank of average $values of Names selected by user and rank the names according to $value. For instance if user selects year Jan22 to Dec22, Count_type Washington and colleague Chandler &Ross the average $value for these two colleagues will be $22mn and the next colleague name Rachel will be totalling to $ 33mn so the rank of these two colleagues should be 2. I am trying to write a dax around this but I am getting an incorrect rank
Click to expand...

Chihiro said:
@Rameez Sarang
Your issue is that you are asking DAX/Data model related question. Yet, your file only contains flat list. With no data model or schema.
DAX is highly contextual in it's nature and will evaluate differently based on context it's evaluated on.

Without knowing your data model structure, no one will be able to give you an answer. After all, we are not a mind reader ;)
Click to expand...
I have been trying to calculate the rank of average $values of Names selected by user and rank the names according to $value. For instance if user selects year Jan22 to Dec22, Count_type Washington and colleague Chandler &Ross the average $value for these two colleagues will be $22mn and the next colleague name Rachel will be totalling to $ 33mn so the rank of these two colleagues should be 2. I am trying to write a dax around this but I am getting an incorrect rank
 
Rameez Sarang said:
I need a Dax query which will give me the average rank for 2022 for Jack+ sheldon combined premium.
Click to expand...
vletm said:

Rameez Sarang​

Did You read all four lines?
Is it only one possible result?
Is it correct?
Click to expand...
I have been trying to calculate the rank of average $values of Names selected by user and rank the names according to $value. For instance if user selects year Jan22 to Dec22, Count_type Washington and colleague Chandler &Ross the average $value for these two colleagues will be $22mn and the next colleague name Rachel will be totalling to $ 33mn so the rank of these two colleagues should be 2. I am trying to write a dax around this but I am getting an incorrect rank
 

Rameez Sarang​

You've basically pasted same writing many times.
Instead continue pasting it ...
Please, reread #7 reply ... reread it twice.
After You've solved that Yourself,
it could be possible that someone could help You.
 
Chihiro said:
@Rameez Sarang
Your issue is that you are asking DAX/Data model related question. Yet, your file only contains flat list. With no data model or schema.
DAX is highly contextual in it's nature and will evaluate differently based on context it's evaluated on.

Without knowing your data model structure, no one will be able to give you an answer. After all, we are not a mind reader ;)
Click to expand...
Please refer attached updated file. I need a DAX for calculating rank for row L 19.
 

Attachments

  • Average_dummy data.xlsx
    15.1 KB · Views: 5
Attached is the power BI file for your refence. Also refer my excel file attached. I need a DAX for calculating rank for row L 19.
 

Attachments

  • Dummy Data for Average Rank.zip
    29.1 KB · Views: 4
You must log in or register to reply here.
Back
Top