Forum Discussion
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) :
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) .
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
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:
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: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])
4 Replies
Output should look like this:
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.
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:
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: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])
