Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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
Solved! Go to 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:
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:
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:
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:
Output should look like this:
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
85 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
132 | |
110 | |
66 | |
55 |