Forum Discussion
Calculate aggregations for each column on the same row
Hello,
I am getting started with Power BI Desktop and was wondering if there was a way to get some agregated fields (in yellow) for each column in the table below. For example, I would like to get the median, the quartile and the average for each value (turnover, Staff, Avg_income) without specifying the value so that I would get all three on the same row.
Thank you
Hi lkalawski , What would be the measures created?
Companies Turnover Staff Avg_income Company 1 1 10 4 Company 2 4 23 3 Company 3 6 19 5 Company 4 7 5 9 Company 5 2 3 4 I have prepared a solution for you.
1. Create additional table with the list of companies and add 3 additional measures:
Company = UNION ( SELECTCOLUMNS ( ALLNOBLANKROW ( Tbl[Companies ] ), "Companies", Tbl[Companies ], "Sort Order", 1 ), { ( "Median" , DISTINCTCOUNTNOBLANK( Tbl[Companies ] ) + 1) }, { ( "Quartile" , DISTINCTCOUNTNOBLANK( Tbl[Companies ] ) + 2) }, { ( "Average" , DISTINCTCOUNTNOBLANK( Tbl[Companies ] ) + 3) } )2. Create 3 measures (I prepared one, but you can do in the same way next measures):
TurnoverM = VAR __selectedCompany = SELECTEDVALUE(Company[Row]) RETURN SWITCH( TRUE(), __selectedCompany IN VALUES(Tbl[Companies ]), CALCULATE(MAX(Tbl[Turnover]), Tbl[Companies ] = __selectedCompany), __selectedCompany = "Median", CALCULATE(MEDIAN(Tbl[Turnover]), ALLNOBLANKROW(Tbl[Companies ])), __selectedCompany = "Quartile", CALCULATE(PERCENTILEX.INC(Tbl,Sum(Tbl[Turnover]),.75), ALLNOBLANKROW(Tbl[Companies ])), __selectedCompany = "Average", CALCULATE(AVERAGE(Tbl[Turnover]), ALLNOBLANKROW(Tbl[Companies ])) )In the matrix, add new list of companies as Rows and Measures as Values:
In the attachment please find the .pbix file.
Proud to be a Super User.
If I helped, please accept the solution and give kudos!
LinkedIN
7 Replies
- lkalawski
Resident Rockstar
I have prepared a solution for you.
1. Create additional table with the list of companies and add 3 additional measures:
Company = UNION ( SELECTCOLUMNS ( ALLNOBLANKROW ( Tbl[Companies ] ), "Companies", Tbl[Companies ], "Sort Order", 1 ), { ( "Median" , DISTINCTCOUNTNOBLANK( Tbl[Companies ] ) + 1) }, { ( "Quartile" , DISTINCTCOUNTNOBLANK( Tbl[Companies ] ) + 2) }, { ( "Average" , DISTINCTCOUNTNOBLANK( Tbl[Companies ] ) + 3) } )2. Create 3 measures (I prepared one, but you can do in the same way next measures):
TurnoverM = VAR __selectedCompany = SELECTEDVALUE(Company[Row]) RETURN SWITCH( TRUE(), __selectedCompany IN VALUES(Tbl[Companies ]), CALCULATE(MAX(Tbl[Turnover]), Tbl[Companies ] = __selectedCompany), __selectedCompany = "Median", CALCULATE(MEDIAN(Tbl[Turnover]), ALLNOBLANKROW(Tbl[Companies ])), __selectedCompany = "Quartile", CALCULATE(PERCENTILEX.INC(Tbl,Sum(Tbl[Turnover]),.75), ALLNOBLANKROW(Tbl[Companies ])), __selectedCompany = "Average", CALCULATE(AVERAGE(Tbl[Turnover]), ALLNOBLANKROW(Tbl[Companies ])) )In the matrix, add new list of companies as Rows and Measures as Values:
In the attachment please find the .pbix file.
Proud to be a Super User.
If I helped, please accept the solution and give kudos!
LinkedINThank you so much lkalawski , it's working. I am still trying to understand the code but it's exactly what I wanted.
- lkalawski
codjoMensah , It's my pleasure.
If you have any questions, please write.
Proud to be a Super User.
If I helped, please accept the solution and give kudos!
LinkedIN
- lkalawski
Hi codjoMensah
You can do it by using matrix, create 8 measures ( 5 for Company and 3 for aggregation) and then use "Show as a row" in the matrix settings.
Please share your sample of data and then I can help you.
Proud to be a Super User.
If I helped, please accept the solution and give kudos!
LinkedIN Hi lkalawski , What would be the measures created?
Companies Turnover Staff Avg_income Company 1 1 10 4 Company 2 4 23 3 Company 3 6 19 5 Company 4 7 5 9 Company 5 2 3 4