Forum Discussion

Saxon202202's avatar
Saxon202202
Helper III
3 years ago

Summarise sum and Max in new table

Hi, I have following columns are in data table Order, Item, Code, Qty, Country code and Tax Code. I am trying to summarise the sum of qty each order, item and code, based on the sum of qty, I am tr...
Need Help
Saxon202202's avatar
Saxon202202
Helper III
3 years ago

Hi, Thanks for your reply and help. 

Can you please advise how can I get the same result by using new table option (DAX) and power query as well. 

  • Saxon202202's avatar
    Saxon202202
    Helper III
    3 years ago

    CNENFRNL, Thanks for your reply. How can I incuding the actual data column instead using measure column in summary table? Please. 

     

    Y

  • Saxon202202's avatar
    Saxon202202
    Helper III
    3 years ago

    CNENFRNL, andhiii079845,

     

    Hi, I created sum of qty column in data table by using the following dax Sum Of Qty = Calculate(sum(data([qty]),

    filter(data,

    data[item] =earlier(data[item]) & &

    data[order] =earlier(data[order])),

    allexpect(data, data[item]))

     

    Now I have sum of qty row level and then I created new table by using this DAX code,

    Table2 =

    summarize(data),

    data[item], data[code],

    "Max Qty", Max(data[SumOfQty]),

    "Country Code" Max(data[contry code]),

    "Tax Code", Max(data[Tax Code]))

     

    attached snapshot for your reference. 

    I got the results (Snapshot table 2)by creating additional columns in Data Table but unable to apply the no blanks conditions in summary table for country code and tax code columns.

     

    I want the same result (Table 2 snapshot) without maintain the additional columns in Data Table also apply non blanks conditions in summary table.