Total of subtotal in the same column

[Anonymous]

Hi all,

I'm new to Power BI and would like to ask for the way of making below report.

So my Data set is like this:

reporting line	Value
H01	10
H02	5
H03	5
H04	10
R01	300
R02	200
R03	100

And I would need to make a report like this:

Total Hours Made	15
H01	10
H02	5
Total Hours Hired	15
H03	5
H04	10
Total Hours	30
Revenue 1	500
R01	300
R02	200
Revenue 2	100
R03	100
Total Revenue	600

It's easy for me to make the "Total Hours Made", "Total Hours Hired", "Revenue 1", "Revenue 2". However, I'm stuck on how to make "Total Hour" and "total Revenue". Could anyone help me on this? 

Apreciate your help.

[kcantor]

@Anonymous 

You should be able to accomplish this by chosing to Show Values on Rows. To find this option, click on the format tool on the visualizations pane (looks like a paint roller), select to expand values, and then turn on Show on Rows. This should give you the view you would like. Keep in mind, you will need to have the matrix visual selected in the workspace in order to apply this step.

[Anonymous]

@kcantor 

Sorry that I'm not really clear in my question. I have edited it. I'm not looking for the way to Show Values on Rows but the way to show subtotal within the same Column. (Column A in my Example. So "total Hours" is the sum of "Hour made" & "Hour hired", "Total Revenue" is the sum of "Revenue 1" & "Revenue 2". I want to show it in the same column with those "Hour made" & "Hour hired"& "Revenue 1" & "Revenue 2")

Thanks in advance.

[kcantor]

@Anonymous 

Can you please define for me what the H01 and H02 designate? My understanding of what I am seeing is that those would be the "Item" or "Product" you are suming time and revenue from. If that is the case, you would place those on the Rows and the Totals would fall under each of them. 

Perhaps adding some additional information would help me to better help you.

[Anonymous]

@kcantor 

Those (H01, H02, H03, H04, R01, R02, R03) are reporting lines in my report. (for example, H01 is chargeable hours made, H02 non-chargeable hours made, H03 is chargeable hours hired, H04 is non-chargeable hours Hired).

What I'm stuck at the moment is that although I can group H01, H02 into "Total Hours Made"; H03 and H04 into "Total Hours Hired" but I could not Group those 2 Groups into "Total Hours" (same for Revenue)

So my Data set is like this:

reporting line	Value
H01	10
H02	5
H03	5
H04	10
R01	300
R02	200
R03	100

And I would need to make a report like this:

Total Hours Made	15
H01	10
H02	5
Total Hours Hired	15
H03	5
H04	10
Total Hours	30
Revenue 1	500
R01	300
R02	200
Revenue 2	100
R03	100
Total Revenue	600

[kcantor]

@Anonymous 

I believe this issue is more related to the shape of your data in your data model. If you create a dimension table that signifies which type of data your reporting line is, this should solve your issue. I created a simple mock up of your data and then captured your reporting lines and created a dimension table assinging them to a simple Type with a calculated column (not good for large projects but IT or a programer could create this in your source data). I then created a relationship between the Table you provided of sample data and this mock up dimension table. Only one calculation was needed at that point which was a simple sum. I used my dimension table to create the subtotals.

My calculation was Total Hours Simple= SUM(Table[Value])

My Dimension Table:

reporting  line                                          Type

H01	Hours Made
H02	Hours Made
H03	Hours Hired
H04	Hours Hired
R01	Revenue 1
R02	Revenue 1
R03	Revenue 2

I created a relationship based upon the reporting line and Dropped the Type on the Rows and the Calculation on the values to produce this:

Granted, you would probably want a seperate calculation for revenue as it would seem to be a different value type. But that could be handled with a filtered calculate from the original simple sum.

Building from that sum, you could create hours, revenue, and then revenue per hour using divide. Please let me know if this helps.

[Anonymous]

@kcantor 

Thanks Kcantor. However, the core problem is that I need to show "Total Hours" line and "Total Revenue" line in your printscreen.

Total Hours Made	15
H01	10
H02	5
Total Hours Hired	15
H03	5
H04	10
Total Hours	30
Revenue 1	500
R01	300
R02	200
Revenue 2	100
R03	100
Total Revenue	600

I'm thinking about SWITCH function (DAX) but not sure whether it works.

Best regards,

[Anonymous]

Up up up. Could anyone help me on this?

[kcantor]

@Anonymous

Sorry for the delay, I was out for a few days.

I still say you can solve this with a dimension table and the shape of your data. You wanted an additional level to show the totals for both Hours and Revenue. I added another level on my ad hoc dimension table and called it Total Type. Then added that to the matrix, keeping all other rows and the same measure from my other offered solution. As you can see, this provides the view you want.