Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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 |
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! |
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! |
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! |
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 |
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! |
@lkalawski They are static data. Only Median, Quartile and average will be measures.
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! |
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |