Forum Discussion

Hesham's avatar
Hesham
Frequent Visitor
8 years ago
Solved

Summarize table and add new column

Hi all i am new in Power BI so, i stopped in few Issues the most important one is: I have huge data table as below: (Details table)   The point i need to Summarize table and add new column ba...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi Hesham,

     

    Create a calculated table using below formula:

    Summarize Table =
ADDCOLUMNS (
    SUMMARIZE (
        Sheet1,
        Sheet1[Cus Phone],
        "Total order", DISTINCTCOUNT ( Sheet1[Order_num] ),
        "Total revenue", SUM ( Sheet1[Item_Rev] )
    ),
    "Flag", IF ( [Total order] <= 2, "Light", IF ( [Total order] >= 5, "High", "Medium" ) )
)

     

    By the way, please mask sensitive data before uploading.

     

    Best regards,
    Yuliana Gu

Abduvali's avatar
Abduvali
Skilled Sharer
8 years ago

Hi Hesham,

 

Try using GROUP BY function in Power BI edit mode:

 

Edit Mode: Select Group BySelect Operation and Value ColumnResult

 

 

 And when you finished just add a new Flag column with your IF statement.

 

For more information see link: Group By Function

 

 

Hope this help.

 

 

Regards

Abduvali

  • Hesham's avatar
    Hesham
    Frequent Visitor
    8 years ago

    Hello Abduvali

    thank you so much for the steps and i need also to add the total Trx per customer in same table and in group by it is not allawed, i checked also the advanced tab but not working with me , kindly your advice to have a table like that consist of (Customer phone-Total orders- Total Revenue)

    • Abduvali's avatar
      Abduvali
      Skilled Sharer
      8 years ago

      How do you calculate TRX per customer?

       

      • if you can please provide some data sample in excel format to speed things up
      • Hesham's avatar
        Hesham
        Frequent Visitor
        8 years ago

        by Order Number because it is Uniqe.