cancel
Showing results for
Did you mean:
Highlighted
New Member

## Calculate a percentage on total from same table with different filter

Hi all,

I'm trying to do something in PowerBI and I'm not sure if it is even possible or whether this is the appropriate place or tool?  Basically, I'm up to my neck in calculations and numbers have lost all meaning ... PLEASE HELP!

I have a table (below) that is displayed twice with different visual level filters on the field Account.    The top one is development items (DEV) and the lower is the total overhead (OV) against all development activity.    I have the total number of hours from timesheet data and have the SUM (Hours (rounded)) and the %GT Hours Rounded.   I now want to multiply %GT Hours Rounded by the total Hours for the overheads.   i.e. for UC-7046 (top row) this would be 0.02% * 405.45

Does this make sense?   Can I do it?  Should I do it?   Is there a better way to do it?

Thanks

K

2 REPLIES 2
Highlighted
Community Champion

Hi There,

Yes, you can do it by creating measure in a single calculation.

For such type of calculations, You should use SUMX function which comes from the iteration family.

This generates the ROW CONTEXT in PBI and interates through one by one row and perform the calculations feed in the arguments.

However, the approach you have selected is not the right approach. your approch can better work in Excel.

Try this measure:

mymeasure:=SUMX(MyTable, MyTable[Hours Rounded]*MyTable[GT Hours Rounded])

To clarify the issue further, this calculation do multiplication row by row instead of row vs totals you suggested.

Let me know if you get stuck.

Thanks & Regards,

Bhavesh

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Highlighted
Community Champion

@KatieRidout

You have 1 table with severals Key Name Some of the represent Development Item and others represent Overhead.

So a Overhead can have many Development Items.

Now, you want to each Development Item would be divide with th SUM of the overheads.

My possible solution to this is:

1. Create a calculated column to filter Development Items and Overheads.

Or in Edit Query with Add Conditional Column.

3.%GT Hours Rounded by the total Hours for the overheads=Table[%GT Hours Rounded Total] * TotalOverheads

Lima - Peru

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors