How to achieve multiple rows in subtotal

[Anonymous]

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)?

[v-xulin-mstf · Accepted solution]

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.

[Anonymous]

v-xulin-mstf  , thank you. Your answer helped me a lot.

Now I am trying to calculate percentage (not sum) within each group.

Then I could eventually make a report that will consist of two matrixes - first with subtotals for each groups and second with grand total.

[Anonymous]

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)
)

[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

[parry2k]

@Anonymous To be very honest, it is not going to be easy to get the output the way you described.