Forum Discussion
Summarizing Table Visual and adding a new column
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])
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])