Group, Sum, and Rank DAX Formula Creation Issue

[Zjacobsmeyer22]

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 following:

Group Expense Line Items by corresponding Expense Report ('DocumentNo' column) number and sum the their 'Amount' columns to get the Total Expense Report value for each report. Then I'd like to perform a RANK function that will rank the Expense Reports by the summed totals generated.

I have been trying to use the SUMMARIZECOLUMN, GROUPBY, SUMMARIZE, SUMX, RANK.EQ, RANKX, formulas, but I am having no luck. Could someone try and help me here?

If I need to provide more information, please let me know.

Thanks,

Zach J

[ibarrau]

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]

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

[v-qiuyu-msft]

Hi @Zjacobsmeyer22,

From your description, it seems that you want to sort the chart DocumentNo based on the SUM(Amount), right?

Power BI provide the sort by feature for the chart, see: Change how a chart is sorted in a Power BI report.

Best Regards,
Qiuyun Yu