Calculate aggregations for each column on the same row

[codjoMensah]

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

 

[codjoMensah · Accepted solution]

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

[lkalawski · Accepted solution]

@codjoMensah 

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

[codjoMensah]

Thank 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

[codjoMensah · Accepted solution]

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

[lkalawski]

@codjoMensah ,

Turnover, Staff and Avg_income - Are these measures or static data?

Proud to be a Super User. If I helped, please accept the solution and give kudos!  LinkedIN

[codjoMensah]

@lkalawski They are static data. Only Median, Quartile and average will be measures. 

[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