Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KatieRidout
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
Vvelarde
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
BhaveshPatel
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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.