Forum Discussion

Zjacobsmeyer22's avatar
Zjacobsmeyer22
New Member
9 years ago

Group, Sum, and Rank DAX Formula Creation Issue

Hi there,   I have a file made up of Expense Report Line Items. I am trying to use the 'DocumentNo' (Expense Report Number) and 'Amount' columns to create a DAX function that will perform the follo...
Need Help
ibarrau's avatar
ibarrau
Super User
9 years ago

I'm not quite sure i get it but let me say something to see if i can help. I think you have to create a columns with the sum of amounts [amount1]+[amount2]+..+[amountN]. Then just create a vertical bar or a table with DocumentNo as axis and the new columns as value. Order it by the sum of amounts and you are ready to go. If the ranking is so necesary, go to filter seccion and use TOPN.

If this was not what you're looking for, just give us more context.

 

Regards,

Zjacobsmeyer22's avatar
Zjacobsmeyer22
New Member
9 years ago

Hi,

 

Thank you for your response Ibarrau. I think I am following what you are saying. Within a Chart, I could make one Axis the 'DocumentNo' (Expense Report Number) and then the 'Amount' field in the Value and do a Sum by Amount function in the Value field. I believe this would then allow me to see which 'DocumentNo' are the largest, smallest, etc.

 

But, at this point, we are trying to cut corners and create an "All-in-One" formula, if you will. We'd first like to create a 'Measurement' value for the 'DocumentNo' column which would Sum the 'Amount' column at every change in 'DocumentNo'. From there we want to be able to create a new column and plug our created'Measurement' into a RANKX (ALL() formula that will provide a ranking from highest to lowest based on the Total Summed Amounts for the different DocumentNos (Expense Reports). I have included Screenshots of the 'DocumentNo' and 'Amount' Column.

 

I know this is probably a bit confusing, but I appreciate all the input/help you all can give.

 

Thanks,

Zach J