Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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
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
Very helpful.
Is it possible to diectly to put the new table into Report as a Visualization Table?
Thank you.
Dennis
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
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")
Hi @Hesham,
Try using GROUP BY function in Power BI edit mode:
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
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)
How do you calculate TRX per customer?
by Order Number because it is Uniqe.
attached as requested in the below link
Follow to the link below to preview the file from my library:
Let me know if thats what you were looking for.
Regards
Abduvali
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
Ah forgot to sum Revenue and Qty:
Updated file
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
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
86 | |
82 | |
64 | |
63 | |
56 |
User | Count |
---|---|
171 | |
113 | |
110 | |
73 | |
73 |