none
Total by quote number RRS feed

  • Question

  • Hi,

    I have a small problem. I have this table:

    First column is the quote number and the second column is the version number. You can see that most quotes have version 0 but some have more than one version (like quote 7707). Third and fourth columns are dates and amount.

    I want to show in a fifth column an average of the quote value which I'll be able to had by distinct quote number. Like example, the three lines of quote 7707 will all show the result: 3718.60 (4630.88+3649.98+2874.95 divided by 3 lines)

    What should be the DAX or M formula?

    Thanks

    Tuesday, February 14, 2017 4:49 PM

Answers

  • In M (Query Editor), you can use Group By (on the Home tab or Transform tab or right-click in a column header):  Group By Quote Number and Average bij Quote Value.

    Edit: additionally, in Group By, you can add a row with Operation "All Rows", which will give you a nested table. As a next step, you can expand this table so you will have your original rows.

    Tuesday, February 14, 2017 4:58 PM

All replies

  • In M (Query Editor), you can use Group By (on the Home tab or Transform tab or right-click in a column header):  Group By Quote Number and Average bij Quote Value.

    Edit: additionally, in Group By, you can add a row with Operation "All Rows", which will give you a nested table. As a next step, you can expand this table so you will have your original rows.

    Tuesday, February 14, 2017 4:58 PM
  • Thank you. It is an interesting feature but it also regroup my date column so I can filter it in time.

    I found a partial solution with a new column and the ALLEXCEPT function in Power BI. Now each line show the average of every quote as you can see on quote 7707.

    Now I just need to show (or add) on record by quote number in my power bi element

    Tuesday, February 14, 2017 7:04 PM
  • "It is an interesting feature but it also regroup my date column so I can filter it in time."

    I don't know what you mean by the above statement, but following from Marcel's description, here's how it's done in Power Query.

    GroupedRows = Table.Group(
                      <previousStepName>, 
                      {"EDV_NDEVIS"}, 
                      {
                        {"Groups", each _, type table}, 
                        {"Average", each List.Average([EDV_PDEVISE]), type number}
                      }
                   ), 
    ExpandedGroups = Table.ExpandTableColumn(
                         GroupedRows, 
                         "Groups", 
                         List.Difference(
                             Table.ColumnNames(GroupedRows[Groups]{0}),
                             {"EDV_NDEVIS"}
                         )
                      )


    Wednesday, February 15, 2017 2:10 AM