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