Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
codjoMensah
Frequent Visitor

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

 

 Capture d’écran 2021-01-26 à 10.30.06.png

2 ACCEPTED SOLUTIONS
codjoMensah
Frequent Visitor

Hi @lkalawski , What would be the measures created? 

 

Companies TurnoverStaffAvg_income
Company 11104
Company 24233
Company 36195
Company 4759
Company 5234

 

View solution in original post

lkalawski
Memorable Member
Memorable Member

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

lkalawski_0-1611659305211.png lkalawski_1-1611659325177.png

In the attachment please find the .pbix file.




PBI_SuperUser_Rank@1x.png Proud to be a Super User.
If I helped, please accept the solution and give kudos
LinkedIN

 

View solution in original post

7 REPLIES 7
lkalawski
Memorable Member
Memorable Member

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

lkalawski_0-1611659305211.png lkalawski_1-1611659325177.png

In the attachment please find the .pbix file.




PBI_SuperUser_Rank@1x.png Proud to be a Super User.
If I helped, please accept the solution and give kudos
LinkedIN

 

Thank you so much @lkalawski , it's working. I am still trying to understand the code but it's exactly what I wanted. 

@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
Frequent Visitor

Hi @lkalawski , What would be the measures created? 

 

Companies TurnoverStaffAvg_income
Company 11104
Company 24233
Company 36195
Company 4759
Company 5234

 

@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

 

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

lkalawski
Memorable Member
Memorable Member

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.