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 based on the source table which will be updated daily as below (Summary Table)

 

 

Thanks In Advance

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

14 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    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

    • Hesham's avatar
      Hesham
      Frequent Visitor
      8 years ago

      WOW that exactly what i need, thank you so much for your efforts and many thanks for all who tried to help i really appreciate your efforts.

       

      Best ragrds

      Hesham

       

    • 12Bowers12's avatar
      12Bowers12
      Helper V
      7 years ago

      Very helpful.

      Is it possible to diectly to put the new table into Report as a Visualization Table?

      Thank you.

      Dennis

  • 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
  • rocky09's avatar
    rocky09
    Solution Sage
    8 years ago

    Select Summary Table --> Select Custom Column and then use this.

     

    Flag = SWITCH(TRUE(),
                SummaryTable[Total Orders]>= 5,"High",
                SummaryTable[Total Orders]<=2,"Light",
                SummaryTable[Total Orders]>=3 && SummaryTable[Total Orders]<5,"Medium")