Forum Discussion
How to achieve multiple rows in subtotal
Hello,
I have learned Power BI for few weeks.
I want to make a report in Power BI, which should look something like this 🙂
Let's assume that all data comes from the Person table, and that when counting absent people, we do not distinguish whether it is a student or a teacher.
Person
(
id
, gender in (M, F)
, position in (student, teacher)
, isPresent in (1, 0)
)
Is it possible to do something like this in Power BI?
I mean whether it is possible to present the summarization in multiple rows (like on picture in few rows below TOTAL row)?
And is it possible to make two-line header in the matrix (as in my picture)?
Hi, @michaelC0
You can use following method to meet your needs:
Step1: Create a table as:
Step2: Create a measure as:
Measure =
IF(ISINSCOPE('Table'[Gender ]),SUM('Table'[Qty]),
var _total=SUMX(VALUES('Table'[Career]),SUM('Table'[Qty]))
var _totalall=SUMX(ALL('Table'),'Table'[Qty])
Return
FORMAT(DIVIDE(_total,_totalall),"percent"))
Here is the output:
Here is the demo, please try it: How to achieve multiple rows in subtotal
Best Regards,
Link
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
- parry2k
Super User
Anonymous To be very honest, it is not going to be easy to get the output the way you described.
- amitchandak
Anonymous , I doubt you can get a display like that easily. Check if there is some custom visual that can help
https://appsource.microsoft.com/en-us/marketplace/apps?product=power-bi-visuals
Another Idea can be that you create few visuals and group them together : https://docs.microsoft.com/en-us/power-bi/desktop-grouping-visuals
Thank you for your response.
Is there a possibility to achieve at least somethink like that:
?
I guess first I need to create some measures:
all_persons = COUNT(Person[id])
It gives 100 (44 + 43 + 5 + 6 + 1+ 1)students = CALCULATE (DISTINCTCOUNT (Person[IsStudent]), FILTER ( Person[IsStudent]= 1))
It gives 87 (44 + 43)teachers = CALCULATE (DISTINCTCOUNT (Person[isTeacher]), FILTER ( Person[isTeacher]= 1))
It gives 11 (5 + 6)But how to create new measure which return proper value (students or teachers) for specific column? I mean 87 for column STUDENT and 11 for column TEACHER.
If I have such measure then I could calculate percentage.In my previous post I forgot to write that I use matrix with fields group and gender in Rows and isStudent, isTeacher in Values.
Person
(
id
, gender in (M, F), group
, isStudent (1, 0)
, isTeacher (1, 0)
, isPresent in (1, 0)
)- v-xulin-mstf
Community Support
Hi, @michaelC0
You can use following method to meet your needs:
Step1: Create a table as:
Step2: Create a measure as:
Measure =
IF(ISINSCOPE('Table'[Gender ]),SUM('Table'[Qty]),
var _total=SUMX(VALUES('Table'[Career]),SUM('Table'[Qty]))
var _totalall=SUMX(ALL('Table'),'Table'[Qty])
Return
FORMAT(DIVIDE(_total,_totalall),"percent"))
Here is the output:
Here is the demo, please try it: How to achieve multiple rows in subtotal
Best Regards,
Link
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
