cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

 

pbi.png

2 REPLIES 2
Highlighted
Community Champion
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.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
Highlighted
Community Champion
Community Champion

@KatieRidout

 

If i understand your Need:

 

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.

 

2. Calculate SUM of Overheads

 

TotalOverheads=Calculate(Sum(Table[Hours(Rounded)];Filter(Table;Type="Overheads")

 

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

 

Maybe with some changes and adjustments this can help you

 

 

 

 




Lima - Peru

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors