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

Hesham's avatar
Hesham
Frequent Visitor
8 years ago

It is not work (all customer is High) and we so far from main subject ...
i need to work mainly on table and no worries about Visualizations..
in the first step it is working fine to sum the revenue (group by) ... so is there any way to do it for the total Trx (distinct count of Order Number by customer phone) then i can make master table or combine both. sorry i am not familiar with BI.

 

Thanks in advance

 

  • Abduvali's avatar
    Abduvali
    Icon for Skilled Sharer rankSkilled Sharer
    8 years ago

    The reason all were HIGH because I did sum Qty in for the FLAG Column if the following is not done then under Cus ID you will have the same customer multiple times. I updated the file in my folder now and you will have multiple customers. But it is correct because the same customer had Ligh, Medium and High orders based on FLAG column.

     

    exp: Basically, the formula will do the following: sum all revenue where the order is >= 5

                                                                                 sum all revenue where the order is <=2

     

    That's why you have customer multiple times.