cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
darko861
Helper II
Helper 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
Ailsa-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])

 

 

 

 

View solution in original post

darko861
Helper II
Helper II

Output should look like this:

 

darko861_0-1626273857054.png

 

 

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.