Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
darko861
Resolver II
Resolver II

Summarizing Table Visual and adding a new column

Hi Community,

 

I would really appreciate it if someone could help me with this task?

 

I have a link to my test BI file: https://www.dropbox.com/s/y59to0mv6qeunyh/Test_Data.pbix?dl=0

 

In the pageTest_Rev_per_hour I have a table that shows the revenue for each Category (Service request, Project, Change request, Transaction and License fee, I have more Categories but the visual has been filtered to show these ones) :

 

darko861_2-1626265861460.png

 

 

On the same page Test_Rev_per_hour, I have a second table that displays the Registered Time (total hours spent for each company). There is a large amount of 26,689.38 which is blank (for the companies that could not be matched) .

 

darko861_3-1626265939570.png

 

Is there a way to incorporate both tables into a single table that will have the following columns:

 

1) Company, Service request, Project, Change request, Transaction, License fee, Registered Time (the blanks must be excluded in the new table), Total amount

 

2) Is there a way to add a new column in the new table above which only summarizes Service request+Project+Change request and divides those columns with the Registered time for each company?

 New Column: Per_hour = (Service request+Project+Change request)/Registered Time

 

I would be really grateful if you could help me with this one, I have tried solving this with some measures, which didn't work, so any guidance would be helpful

 

Best regards,

Darko

1 ACCEPTED SOLUTION

Hi,

 

I have finally solved this difficult task with some trial and error.

 

Firstly, you'll need to create a separate (manual) table that can collect all the necessary data from the company table and some measures that I have already created. It's like creating a PivotTable, that looks like this:

 

darko861_1-1626866686383.png

 

 

The code I have used to create the PivotTable as shown above is this one:

 

PivotTable = SUMMARIZECOLUMNS(PUFIRMA[D2021_Foeretag],PUFIRMA[D2037_Landkod],PUFIRMA[D2111_Kundtyp],DimDate[Date].[Date],"Service request",CALCULATE([Amount Rev sign],FILTER(Categories,Categories[Categories]="Service request")),"Project",CALCULATE([Amount Rev sign],FILTER(Categories,Categories[Categories]="Project")),"Transaction",CALCULATE([Amount Rev sign],FILTER(Categories,Categories[Categories]="Transaction")),"Change request",CALCULATE([Amount Rev sign],FILTER(Categories,Categories[Categories]="Change request")),"License fee y/m",CALCULATE([Amount Rev sign],FILTER(Categories,Categories[Categories]="License fee y/m")),"Revenue",[Amount Rev sign],"Registered Time",[Registered Time])
 
 
After you have created the above table, it will be possible the display the desired output:
 
darko861_2-1626866895724.png

 

darko861_3-1626867174002.png

 

For the visual to properly summarize the columns Service request, Project, and Change request and then divide it by the correct Registered time, I have created three measures:

 

xxx_rev_per_hour1 = [xxxrev1]/[xxxrev1.0]
 
xxxrev1 = SUMX(SUMMARIZE(PivotTable,PivotTable[D2021_Foeretag],"_1",SUMX(PivotTable,PivotTable[Service request]+PivotTable[Change request]+PivotTable[Project])),[_1])
 
xxxrev1.0 = SUMX(SUMMARIZE(PivotTable,PivotTable[D2021_Foeretag],"_1",SUM(PivotTable[Registered Time])),[_1])

 

 

 

 

View solution in original post

4 REPLIES 4
v-yetao1-msft
Community Support
Community Support

Hi @darko861 

You can first combine the two tables into one table, and then divide the contents of the category into multiple columns through Pivot Column .

You can refer to these links to combine multiple tables into one .

https://trumpexcel.com/merge-tables/

https://www.c-sharpcorner.com/article/merge-two-tables-in-power-bi/

You can refer to the link to see how to use Pivot

https://databear.com/power-bi-pivot-and-unpivot-columns/

 

Best Regards

Community Support Team _ Ailsa Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi, this will not work, both matrixes come from different data sources, these tables don't exist, I have created them just as visuals. In this case, it's too complicated for Power Query. The only solution so far is that I will use, two matrixes that will have the necessary data as shown below.

 

darko861_0-1626709955589.png

 

Hi,

 

I have finally solved this difficult task with some trial and error.

 

Firstly, you'll need to create a separate (manual) table that can collect all the necessary data from the company table and some measures that I have already created. It's like creating a PivotTable, that looks like this:

 

darko861_1-1626866686383.png

 

 

The code I have used to create the PivotTable as shown above is this one:

 

PivotTable = SUMMARIZECOLUMNS(PUFIRMA[D2021_Foeretag],PUFIRMA[D2037_Landkod],PUFIRMA[D2111_Kundtyp],DimDate[Date].[Date],"Service request",CALCULATE([Amount Rev sign],FILTER(Categories,Categories[Categories]="Service request")),"Project",CALCULATE([Amount Rev sign],FILTER(Categories,Categories[Categories]="Project")),"Transaction",CALCULATE([Amount Rev sign],FILTER(Categories,Categories[Categories]="Transaction")),"Change request",CALCULATE([Amount Rev sign],FILTER(Categories,Categories[Categories]="Change request")),"License fee y/m",CALCULATE([Amount Rev sign],FILTER(Categories,Categories[Categories]="License fee y/m")),"Revenue",[Amount Rev sign],"Registered Time",[Registered Time])
 
 
After you have created the above table, it will be possible the display the desired output:
 
darko861_2-1626866895724.png

 

darko861_3-1626867174002.png

 

For the visual to properly summarize the columns Service request, Project, and Change request and then divide it by the correct Registered time, I have created three measures:

 

xxx_rev_per_hour1 = [xxxrev1]/[xxxrev1.0]
 
xxxrev1 = SUMX(SUMMARIZE(PivotTable,PivotTable[D2021_Foeretag],"_1",SUMX(PivotTable,PivotTable[Service request]+PivotTable[Change request]+PivotTable[Project])),[_1])
 
xxxrev1.0 = SUMX(SUMMARIZE(PivotTable,PivotTable[D2021_Foeretag],"_1",SUM(PivotTable[Registered Time])),[_1])

 

 

 

 

darko861
Resolver II
Resolver II

Output should look like this:

 

darko861_0-1626273857054.png

 

 

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.