Summarizing Table Visual and adding a new column

[darko861]

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

[v-yetao1-msft]

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.

[darko861]

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 · 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:

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

[darko861]

Output should look like this: