Summarize table and add new column

[Hesham]

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

[v-yulgu-msft · Accepted solution]

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

[12Bowers12]

Very helpful.

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

Thank you.

Dennis

[Hesham]

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

[rocky09]

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")

[Abduvali]

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]

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]

How do you calculate TRX per customer?

• if you can please provide some data sample in excel format to speed things up

[Hesham]

by Order Number because it is Uniqe.

[Hesham]

attached as requested in the below link

https://wetransfer.com/downloads/3ad6489094f05dff38b6ae8d495a37e320171107130013/2b245d9aa18b073ab5d9...

[Abduvali]

Follow to the link below to preview the file from my library:

• Orders file

Let me know if thats what you were looking for.

Regards

Abduvali

[Hesham]

First of all thank you so much but in the duplicate table same customer number Classed as high /medium

want i need is sample source table (full details Table) then summary table have the reuired column (Customer Phone-total Revenue-Total trx ).

after that i can add measures and visualizations charts

[Abduvali]

Ah forgot to sum Revenue and Qty:

• https://www.pbiusergroup.com/viewdocument/orders-group-by-function-update

Updated file

[Hesham]

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]

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.