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

Need Help

Tips and Tricks

codjoMensah
5 years ago
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

5 years ago

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: